1"""
   2## Expressions
   3
   4Every AST node in SQLGlot is represented by a subclass of `Expression`.
   5
   6This module contains the implementation of all supported `Expression` types. Additionally,
   7it exposes a number of helper functions, which are mainly used to programmatically build
   8SQL expressions, such as `sqlglot.expressions.select`.
   9
  10----
  11"""
  12
  13from __future__ import annotations
  14
  15import datetime
  16import math
  17import numbers
  18import re
  19import typing as t
  20from collections import deque
  21from copy import deepcopy
  22from enum import auto
  23from functools import reduce
  24
  25from sqlglot._typing import E
  26from sqlglot.errors import ErrorLevel, ParseError
  27from sqlglot.helper import (
  28    AutoName,
  29    camel_to_snake_case,
  30    ensure_collection,
  31    ensure_list,
  32    seq_get,
  33    subclasses,
  34)
  35from sqlglot.tokens import Token
  36
  37if t.TYPE_CHECKING:
  38    from sqlglot.dialects.dialect import DialectType
  39
  40
  41class _Expression(type):
  42    def __new__(cls, clsname, bases, attrs):
  43        klass = super().__new__(cls, clsname, bases, attrs)
  44
  45        # When an Expression class is created, its key is automatically set to be
  46        # the lowercase version of the class' name.
  47        klass.key = clsname.lower()
  48
  49        # This is so that docstrings are not inherited in pdoc
  50        klass.__doc__ = klass.__doc__ or ""
  51
  52        return klass
  53
  54
  55SQLGLOT_META = "sqlglot.meta"
  56
  57
  58class Expression(metaclass=_Expression):
  59    """
  60    The base class for all expressions in a syntax tree. Each Expression encapsulates any necessary
  61    context, such as its child expressions, their names (arg keys), and whether a given child expression
  62    is optional or not.
  63
  64    Attributes:
  65        key: a unique key for each class in the Expression hierarchy. This is useful for hashing
  66            and representing expressions as strings.
  67        arg_types: determines what arguments (child nodes) are supported by an expression. It
  68            maps arg keys to booleans that indicate whether the corresponding args are optional.
  69        parent: a reference to the parent expression (or None, in case of root expressions).
  70        arg_key: the arg key an expression is associated with, i.e. the name its parent expression
  71            uses to refer to it.
  72        comments: a list of comments that are associated with a given expression. This is used in
  73            order to preserve comments when transpiling SQL code.
  74        type: the `sqlglot.expressions.DataType` type of an expression. This is inferred by the
  75            optimizer, in order to enable some transformations that require type information.
  76        meta: a dictionary that can be used to store useful metadata for a given expression.
  77
  78    Example:
  79        >>> class Foo(Expression):
  80        ...     arg_types = {"this": True, "expression": False}
  81
  82        The above definition informs us that Foo is an Expression that requires an argument called
  83        "this" and may also optionally receive an argument called "expression".
  84
  85    Args:
  86        args: a mapping used for retrieving the arguments of an expression, given their arg keys.
  87    """
  88
  89    key = "expression"
  90    arg_types = {"this": True}
  91    __slots__ = ("args", "parent", "arg_key", "comments", "_type", "_meta", "_hash")
  92
  93    def __init__(self, **args: t.Any):
  94        self.args: t.Dict[str, t.Any] = args
  95        self.parent: t.Optional[Expression] = None
  96        self.arg_key: t.Optional[str] = None
  97        self.comments: t.Optional[t.List[str]] = None
  98        self._type: t.Optional[DataType] = None
  99        self._meta: t.Optional[t.Dict[str, t.Any]] = None
 100        self._hash: t.Optional[int] = None
 101
 102        for arg_key, value in self.args.items():
 103            self._set_parent(arg_key, value)
 104
 105    def __eq__(self, other) -> bool:
 106        return type(self) is type(other) and hash(self) == hash(other)
 107
 108    @property
 109    def hashable_args(self) -> t.Any:
 110        return frozenset(
 111            (k, tuple(_norm_arg(a) for a in v) if type(v) is list else _norm_arg(v))
 112            for k, v in self.args.items()
 113            if not (v is None or v is False or (type(v) is list and not v))
 114        )
 115
 116    def __hash__(self) -> int:
 117        if self._hash is not None:
 118            return self._hash
 119
 120        return hash((self.__class__, self.hashable_args))
 121
 122    @property
 123    def this(self) -> t.Any:
 124        """
 125        Retrieves the argument with key "this".
 126        """
 127        return self.args.get("this")
 128
 129    @property
 130    def expression(self) -> t.Any:
 131        """
 132        Retrieves the argument with key "expression".
 133        """
 134        return self.args.get("expression")
 135
 136    @property
 137    def expressions(self) -> t.List[t.Any]:
 138        """
 139        Retrieves the argument with key "expressions".
 140        """
 141        return self.args.get("expressions") or []
 142
 143    def text(self, key) -> str:
 144        """
 145        Returns a textual representation of the argument corresponding to "key". This can only be used
 146        for args that are strings or leaf Expression instances, such as identifiers and literals.
 147        """
 148        field = self.args.get(key)
 149        if isinstance(field, str):
 150            return field
 151        if isinstance(field, (Identifier, Literal, Var)):
 152            return field.this
 153        if isinstance(field, (Star, Null)):
 154            return field.name
 155        return ""
 156
 157    @property
 158    def is_string(self) -> bool:
 159        """
 160        Checks whether a Literal expression is a string.
 161        """
 162        return isinstance(self, Literal) and self.args["is_string"]
 163
 164    @property
 165    def is_number(self) -> bool:
 166        """
 167        Checks whether a Literal expression is a number.
 168        """
 169        return isinstance(self, Literal) and not self.args["is_string"]
 170
 171    @property
 172    def is_int(self) -> bool:
 173        """
 174        Checks whether a Literal expression is an integer.
 175        """
 176        if self.is_number:
 177            try:
 178                int(self.name)
 179                return True
 180            except ValueError:
 181                pass
 182        return False
 183
 184    @property
 185    def is_star(self) -> bool:
 186        """Checks whether an expression is a star."""
 187        return isinstance(self, Star) or (isinstance(self, Column) and isinstance(self.this, Star))
 188
 189    @property
 190    def alias(self) -> str:
 191        """
 192        Returns the alias of the expression, or an empty string if it's not aliased.
 193        """
 194        if isinstance(self.args.get("alias"), TableAlias):
 195            return self.args["alias"].name
 196        return self.text("alias")
 197
 198    @property
 199    def alias_column_names(self) -> t.List[str]:
 200        table_alias = self.args.get("alias")
 201        if not table_alias:
 202            return []
 203        return [c.name for c in table_alias.args.get("columns") or []]
 204
 205    @property
 206    def name(self) -> str:
 207        return self.text("this")
 208
 209    @property
 210    def alias_or_name(self) -> str:
 211        return self.alias or self.name
 212
 213    @property
 214    def output_name(self) -> str:
 215        """
 216        Name of the output column if this expression is a selection.
 217
 218        If the Expression has no output name, an empty string is returned.
 219
 220        Example:
 221            >>> from sqlglot import parse_one
 222            >>> parse_one("SELECT a").expressions[0].output_name
 223            'a'
 224            >>> parse_one("SELECT b AS c").expressions[0].output_name
 225            'c'
 226            >>> parse_one("SELECT 1 + 2").expressions[0].output_name
 227            ''
 228        """
 229        return ""
 230
 231    @property
 232    def type(self) -> t.Optional[DataType]:
 233        return self._type
 234
 235    @type.setter
 236    def type(self, dtype: t.Optional[DataType | DataType.Type | str]) -> None:
 237        if dtype and not isinstance(dtype, DataType):
 238            dtype = DataType.build(dtype)
 239        self._type = dtype  # type: ignore
 240
 241    def is_type(self, *dtypes) -> bool:
 242        return self.type is not None and self.type.is_type(*dtypes)
 243
 244    @property
 245    def meta(self) -> t.Dict[str, t.Any]:
 246        if self._meta is None:
 247            self._meta = {}
 248        return self._meta
 249
 250    def __deepcopy__(self, memo):
 251        copy = self.__class__(**deepcopy(self.args))
 252        if self.comments is not None:
 253            copy.comments = deepcopy(self.comments)
 254
 255        if self._type is not None:
 256            copy._type = self._type.copy()
 257
 258        if self._meta is not None:
 259            copy._meta = deepcopy(self._meta)
 260
 261        return copy
 262
 263    def copy(self):
 264        """
 265        Returns a deep copy of the expression.
 266        """
 267        new = deepcopy(self)
 268        new.parent = self.parent
 269        return new
 270
 271    def add_comments(self, comments: t.Optional[t.List[str]]) -> None:
 272        if self.comments is None:
 273            self.comments = []
 274        if comments:
 275            for comment in comments:
 276                _, *meta = comment.split(SQLGLOT_META)
 277                if meta:
 278                    for kv in "".join(meta).split(","):
 279                        k, *v = kv.split("=")
 280                        value = v[0].strip() if v else True
 281                        self.meta[k.strip()] = value
 282                self.comments.append(comment)
 283
 284    def append(self, arg_key: str, value: t.Any) -> None:
 285        """
 286        Appends value to arg_key if it's a list or sets it as a new list.
 287
 288        Args:
 289            arg_key (str): name of the list expression arg
 290            value (Any): value to append to the list
 291        """
 292        if not isinstance(self.args.get(arg_key), list):
 293            self.args[arg_key] = []
 294        self.args[arg_key].append(value)
 295        self._set_parent(arg_key, value)
 296
 297    def set(self, arg_key: str, value: t.Any) -> None:
 298        """
 299        Sets arg_key to value.
 300
 301        Args:
 302            arg_key: name of the expression arg.
 303            value: value to set the arg to.
 304        """
 305        if value is None:
 306            self.args.pop(arg_key, None)
 307            return
 308
 309        self.args[arg_key] = value
 310        self._set_parent(arg_key, value)
 311
 312    def _set_parent(self, arg_key: str, value: t.Any) -> None:
 313        if hasattr(value, "parent"):
 314            value.parent = self
 315            value.arg_key = arg_key
 316        elif type(value) is list:
 317            for v in value:
 318                if hasattr(v, "parent"):
 319                    v.parent = self
 320                    v.arg_key = arg_key
 321
 322    @property
 323    def depth(self) -> int:
 324        """
 325        Returns the depth of this tree.
 326        """
 327        if self.parent:
 328            return self.parent.depth + 1
 329        return 0
 330
 331    def iter_expressions(self) -> t.Iterator[t.Tuple[str, Expression]]:
 332        """Yields the key and expression for all arguments, exploding list args."""
 333        for k, vs in self.args.items():
 334            if type(vs) is list:
 335                for v in vs:
 336                    if hasattr(v, "parent"):
 337                        yield k, v
 338            else:
 339                if hasattr(vs, "parent"):
 340                    yield k, vs
 341
 342    def find(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Optional[E]:
 343        """
 344        Returns the first node in this tree which matches at least one of
 345        the specified types.
 346
 347        Args:
 348            expression_types: the expression type(s) to match.
 349            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 350
 351        Returns:
 352            The node which matches the criteria or None if no such node was found.
 353        """
 354        return next(self.find_all(*expression_types, bfs=bfs), None)
 355
 356    def find_all(self, *expression_types: t.Type[E], bfs: bool = True) -> t.Iterator[E]:
 357        """
 358        Returns a generator object which visits all nodes in this tree and only
 359        yields those that match at least one of the specified expression types.
 360
 361        Args:
 362            expression_types: the expression type(s) to match.
 363            bfs: whether to search the AST using the BFS algorithm (DFS is used if false).
 364
 365        Returns:
 366            The generator object.
 367        """
 368        for expression, *_ in self.walk(bfs=bfs):
 369            if isinstance(expression, expression_types):
 370                yield expression
 371
 372    def find_ancestor(self, *expression_types: t.Type[E]) -> t.Optional[E]:
 373        """
 374        Returns a nearest parent matching expression_types.
 375
 376        Args:
 377            expression_types: the expression type(s) to match.
 378
 379        Returns:
 380            The parent node.
 381        """
 382        ancestor = self.parent
 383        while ancestor and not isinstance(ancestor, expression_types):
 384            ancestor = ancestor.parent
 385        return t.cast(E, ancestor)
 386
 387    @property
 388    def parent_select(self) -> t.Optional[Select]:
 389        """
 390        Returns the parent select statement.
 391        """
 392        return self.find_ancestor(Select)
 393
 394    @property
 395    def same_parent(self) -> bool:
 396        """Returns if the parent is the same class as itself."""
 397        return type(self.parent) is self.__class__
 398
 399    def root(self) -> Expression:
 400        """
 401        Returns the root expression of this tree.
 402        """
 403        expression = self
 404        while expression.parent:
 405            expression = expression.parent
 406        return expression
 407
 408    def walk(self, bfs=True, prune=None):
 409        """
 410        Returns a generator object which visits all nodes in this tree.
 411
 412        Args:
 413            bfs (bool): if set to True the BFS traversal order will be applied,
 414                otherwise the DFS traversal will be used instead.
 415            prune ((node, parent, arg_key) -> bool): callable that returns True if
 416                the generator should stop traversing this branch of the tree.
 417
 418        Returns:
 419            the generator object.
 420        """
 421        if bfs:
 422            yield from self.bfs(prune=prune)
 423        else:
 424            yield from self.dfs(prune=prune)
 425
 426    def dfs(self, parent=None, key=None, prune=None):
 427        """
 428        Returns a generator object which visits all nodes in this tree in
 429        the DFS (Depth-first) order.
 430
 431        Returns:
 432            The generator object.
 433        """
 434        parent = parent or self.parent
 435        yield self, parent, key
 436        if prune and prune(self, parent, key):
 437            return
 438
 439        for k, v in self.iter_expressions():
 440            yield from v.dfs(self, k, prune)
 441
 442    def bfs(self, prune=None):
 443        """
 444        Returns a generator object which visits all nodes in this tree in
 445        the BFS (Breadth-first) order.
 446
 447        Returns:
 448            The generator object.
 449        """
 450        queue = deque([(self, self.parent, None)])
 451
 452        while queue:
 453            item, parent, key = queue.popleft()
 454
 455            yield item, parent, key
 456            if prune and prune(item, parent, key):
 457                continue
 458
 459            for k, v in item.iter_expressions():
 460                queue.append((v, item, k))
 461
 462    def unnest(self):
 463        """
 464        Returns the first non parenthesis child or self.
 465        """
 466        expression = self
 467        while type(expression) is Paren:
 468            expression = expression.this
 469        return expression
 470
 471    def unalias(self):
 472        """
 473        Returns the inner expression if this is an Alias.
 474        """
 475        if isinstance(self, Alias):
 476            return self.this
 477        return self
 478
 479    def unnest_operands(self):
 480        """
 481        Returns unnested operands as a tuple.
 482        """
 483        return tuple(arg.unnest() for _, arg in self.iter_expressions())
 484
 485    def flatten(self, unnest=True):
 486        """
 487        Returns a generator which yields child nodes who's parents are the same class.
 488
 489        A AND B AND C -> [A, B, C]
 490        """
 491        for node, _, _ in self.dfs(prune=lambda n, p, *_: p and not type(n) is self.__class__):
 492            if not type(node) is self.__class__:
 493                yield node.unnest() if unnest and not isinstance(node, Subquery) else node
 494
 495    def __str__(self) -> str:
 496        return self.sql()
 497
 498    def __repr__(self) -> str:
 499        return self._to_s()
 500
 501    def sql(self, dialect: DialectType = None, **opts) -> str:
 502        """
 503        Returns SQL string representation of this tree.
 504
 505        Args:
 506            dialect: the dialect of the output SQL string (eg. "spark", "hive", "presto", "mysql").
 507            opts: other `sqlglot.generator.Generator` options.
 508
 509        Returns:
 510            The SQL string.
 511        """
 512        from sqlglot.dialects import Dialect
 513
 514        return Dialect.get_or_raise(dialect)().generate(self, **opts)
 515
 516    def _to_s(self, hide_missing: bool = True, level: int = 0) -> str:
 517        indent = "" if not level else "\n"
 518        indent += "".join(["  "] * level)
 519        left = f"({self.key.upper()} "
 520
 521        args: t.Dict[str, t.Any] = {
 522            k: ", ".join(
 523                v._to_s(hide_missing=hide_missing, level=level + 1)
 524                if hasattr(v, "_to_s")
 525                else str(v)
 526                for v in ensure_list(vs)
 527                if v is not None
 528            )
 529            for k, vs in self.args.items()
 530        }
 531        args["comments"] = self.comments
 532        args["type"] = self.type
 533        args = {k: v for k, v in args.items() if v or not hide_missing}
 534
 535        right = ", ".join(f"{k}: {v}" for k, v in args.items())
 536        right += ")"
 537
 538        return indent + left + right
 539
 540    def transform(self, fun, *args, copy=True, **kwargs):
 541        """
 542        Recursively visits all tree nodes (excluding already transformed ones)
 543        and applies the given transformation function to each node.
 544
 545        Args:
 546            fun (function): a function which takes a node as an argument and returns a
 547                new transformed node or the same node without modifications. If the function
 548                returns None, then the corresponding node will be removed from the syntax tree.
 549            copy (bool): if set to True a new tree instance is constructed, otherwise the tree is
 550                modified in place.
 551
 552        Returns:
 553            The transformed tree.
 554        """
 555        node = self.copy() if copy else self
 556        new_node = fun(node, *args, **kwargs)
 557
 558        if new_node is None or not isinstance(new_node, Expression):
 559            return new_node
 560        if new_node is not node:
 561            new_node.parent = node.parent
 562            return new_node
 563
 564        replace_children(new_node, lambda child: child.transform(fun, *args, copy=False, **kwargs))
 565        return new_node
 566
 567    @t.overload
 568    def replace(self, expression: E) -> E:
 569        ...
 570
 571    @t.overload
 572    def replace(self, expression: None) -> None:
 573        ...
 574
 575    def replace(self, expression):
 576        """
 577        Swap out this expression with a new expression.
 578
 579        For example::
 580
 581            >>> tree = Select().select("x").from_("tbl")
 582            >>> tree.find(Column).replace(Column(this="y"))
 583            (COLUMN this: y)
 584            >>> tree.sql()
 585            'SELECT y FROM tbl'
 586
 587        Args:
 588            expression: new node
 589
 590        Returns:
 591            The new expression or expressions.
 592        """
 593        if not self.parent:
 594            return expression
 595
 596        parent = self.parent
 597        self.parent = None
 598
 599        replace_children(parent, lambda child: expression if child is self else child)
 600        return expression
 601
 602    def pop(self: E) -> E:
 603        """
 604        Remove this expression from its AST.
 605
 606        Returns:
 607            The popped expression.
 608        """
 609        self.replace(None)
 610        return self
 611
 612    def assert_is(self, type_: t.Type[E]) -> E:
 613        """
 614        Assert that this `Expression` is an instance of `type_`.
 615
 616        If it is NOT an instance of `type_`, this raises an assertion error.
 617        Otherwise, this returns this expression.
 618
 619        Examples:
 620            This is useful for type security in chained expressions:
 621
 622            >>> import sqlglot
 623            >>> sqlglot.parse_one("SELECT x from y").assert_is(Select).select("z").sql()
 624            'SELECT x, z FROM y'
 625        """
 626        assert isinstance(self, type_)
 627        return self
 628
 629    def error_messages(self, args: t.Optional[t.Sequence] = None) -> t.List[str]:
 630        """
 631        Checks if this expression is valid (e.g. all mandatory args are set).
 632
 633        Args:
 634            args: a sequence of values that were used to instantiate a Func expression. This is used
 635                to check that the provided arguments don't exceed the function argument limit.
 636
 637        Returns:
 638            A list of error messages for all possible errors that were found.
 639        """
 640        errors: t.List[str] = []
 641
 642        for k in self.args:
 643            if k not in self.arg_types:
 644                errors.append(f"Unexpected keyword: '{k}' for {self.__class__}")
 645        for k, mandatory in self.arg_types.items():
 646            v = self.args.get(k)
 647            if mandatory and (v is None or (isinstance(v, list) and not v)):
 648                errors.append(f"Required keyword: '{k}' missing for {self.__class__}")
 649
 650        if (
 651            args
 652            and isinstance(self, Func)
 653            and len(args) > len(self.arg_types)
 654            and not self.is_var_len_args
 655        ):
 656            errors.append(
 657                f"The number of provided arguments ({len(args)}) is greater than "
 658                f"the maximum number of supported arguments ({len(self.arg_types)})"
 659            )
 660
 661        return errors
 662
 663    def dump(self):
 664        """
 665        Dump this Expression to a JSON-serializable dict.
 666        """
 667        from sqlglot.serde import dump
 668
 669        return dump(self)
 670
 671    @classmethod
 672    def load(cls, obj):
 673        """
 674        Load a dict (as returned by `Expression.dump`) into an Expression instance.
 675        """
 676        from sqlglot.serde import load
 677
 678        return load(obj)
 679
 680    def and_(
 681        self,
 682        *expressions: t.Optional[ExpOrStr],
 683        dialect: DialectType = None,
 684        copy: bool = True,
 685        **opts,
 686    ) -> Condition:
 687        """
 688        AND this condition with one or multiple expressions.
 689
 690        Example:
 691            >>> condition("x=1").and_("y=1").sql()
 692            'x = 1 AND y = 1'
 693
 694        Args:
 695            *expressions: the SQL code strings to parse.
 696                If an `Expression` instance is passed, it will be used as-is.
 697            dialect: the dialect used to parse the input expression.
 698            copy: whether or not to copy the involved expressions (only applies to Expressions).
 699            opts: other options to use to parse the input expressions.
 700
 701        Returns:
 702            The new And condition.
 703        """
 704        return and_(self, *expressions, dialect=dialect, copy=copy, **opts)
 705
 706    def or_(
 707        self,
 708        *expressions: t.Optional[ExpOrStr],
 709        dialect: DialectType = None,
 710        copy: bool = True,
 711        **opts,
 712    ) -> Condition:
 713        """
 714        OR this condition with one or multiple expressions.
 715
 716        Example:
 717            >>> condition("x=1").or_("y=1").sql()
 718            'x = 1 OR y = 1'
 719
 720        Args:
 721            *expressions: the SQL code strings to parse.
 722                If an `Expression` instance is passed, it will be used as-is.
 723            dialect: the dialect used to parse the input expression.
 724            copy: whether or not to copy the involved expressions (only applies to Expressions).
 725            opts: other options to use to parse the input expressions.
 726
 727        Returns:
 728            The new Or condition.
 729        """
 730        return or_(self, *expressions, dialect=dialect, copy=copy, **opts)
 731
 732    def not_(self, copy: bool = True):
 733        """
 734        Wrap this condition with NOT.
 735
 736        Example:
 737            >>> condition("x=1").not_().sql()
 738            'NOT x = 1'
 739
 740        Args:
 741            copy: whether or not to copy this object.
 742
 743        Returns:
 744            The new Not instance.
 745        """
 746        return not_(self, copy=copy)
 747
 748    def as_(
 749        self,
 750        alias: str | Identifier,
 751        quoted: t.Optional[bool] = None,
 752        dialect: DialectType = None,
 753        copy: bool = True,
 754        **opts,
 755    ) -> Alias:
 756        return alias_(self, alias, quoted=quoted, dialect=dialect, copy=copy, **opts)
 757
 758    def _binop(self, klass: t.Type[E], other: t.Any, reverse: bool = False) -> E:
 759        this = self.copy()
 760        other = convert(other, copy=True)
 761        if not isinstance(this, klass) and not isinstance(other, klass):
 762            this = _wrap(this, Binary)
 763            other = _wrap(other, Binary)
 764        if reverse:
 765            return klass(this=other, expression=this)
 766        return klass(this=this, expression=other)
 767
 768    def __getitem__(self, other: ExpOrStr | t.Tuple[ExpOrStr]) -> Bracket:
 769        return Bracket(
 770            this=self.copy(), expressions=[convert(e, copy=True) for e in ensure_list(other)]
 771        )
 772
 773    def __iter__(self) -> t.Iterator:
 774        if "expressions" in self.arg_types:
 775            return iter(self.args.get("expressions") or [])
 776        # We define this because __getitem__ converts Expression into an iterable, which is
 777        # problematic because one can hit infinite loops if they do "for x in some_expr: ..."
 778        # See: https://peps.python.org/pep-0234/
 779        raise TypeError(f"'{self.__class__.__name__}' object is not iterable")
 780
 781    def isin(
 782        self,
 783        *expressions: t.Any,
 784        query: t.Optional[ExpOrStr] = None,
 785        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
 786        copy: bool = True,
 787        **opts,
 788    ) -> In:
 789        return In(
 790            this=maybe_copy(self, copy),
 791            expressions=[convert(e, copy=copy) for e in expressions],
 792            query=maybe_parse(query, copy=copy, **opts) if query else None,
 793            unnest=Unnest(
 794                expressions=[
 795                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
 796                ]
 797            )
 798            if unnest
 799            else None,
 800        )
 801
 802    def between(self, low: t.Any, high: t.Any, copy: bool = True, **opts) -> Between:
 803        return Between(
 804            this=maybe_copy(self, copy),
 805            low=convert(low, copy=copy, **opts),
 806            high=convert(high, copy=copy, **opts),
 807        )
 808
 809    def is_(self, other: ExpOrStr) -> Is:
 810        return self._binop(Is, other)
 811
 812    def like(self, other: ExpOrStr) -> Like:
 813        return self._binop(Like, other)
 814
 815    def ilike(self, other: ExpOrStr) -> ILike:
 816        return self._binop(ILike, other)
 817
 818    def eq(self, other: t.Any) -> EQ:
 819        return self._binop(EQ, other)
 820
 821    def neq(self, other: t.Any) -> NEQ:
 822        return self._binop(NEQ, other)
 823
 824    def rlike(self, other: ExpOrStr) -> RegexpLike:
 825        return self._binop(RegexpLike, other)
 826
 827    def div(self, other: ExpOrStr, typed: bool = False, safe: bool = False) -> Div:
 828        div = self._binop(Div, other)
 829        div.args["typed"] = typed
 830        div.args["safe"] = safe
 831        return div
 832
 833    def __lt__(self, other: t.Any) -> LT:
 834        return self._binop(LT, other)
 835
 836    def __le__(self, other: t.Any) -> LTE:
 837        return self._binop(LTE, other)
 838
 839    def __gt__(self, other: t.Any) -> GT:
 840        return self._binop(GT, other)
 841
 842    def __ge__(self, other: t.Any) -> GTE:
 843        return self._binop(GTE, other)
 844
 845    def __add__(self, other: t.Any) -> Add:
 846        return self._binop(Add, other)
 847
 848    def __radd__(self, other: t.Any) -> Add:
 849        return self._binop(Add, other, reverse=True)
 850
 851    def __sub__(self, other: t.Any) -> Sub:
 852        return self._binop(Sub, other)
 853
 854    def __rsub__(self, other: t.Any) -> Sub:
 855        return self._binop(Sub, other, reverse=True)
 856
 857    def __mul__(self, other: t.Any) -> Mul:
 858        return self._binop(Mul, other)
 859
 860    def __rmul__(self, other: t.Any) -> Mul:
 861        return self._binop(Mul, other, reverse=True)
 862
 863    def __truediv__(self, other: t.Any) -> Div:
 864        return self._binop(Div, other)
 865
 866    def __rtruediv__(self, other: t.Any) -> Div:
 867        return self._binop(Div, other, reverse=True)
 868
 869    def __floordiv__(self, other: t.Any) -> IntDiv:
 870        return self._binop(IntDiv, other)
 871
 872    def __rfloordiv__(self, other: t.Any) -> IntDiv:
 873        return self._binop(IntDiv, other, reverse=True)
 874
 875    def __mod__(self, other: t.Any) -> Mod:
 876        return self._binop(Mod, other)
 877
 878    def __rmod__(self, other: t.Any) -> Mod:
 879        return self._binop(Mod, other, reverse=True)
 880
 881    def __pow__(self, other: t.Any) -> Pow:
 882        return self._binop(Pow, other)
 883
 884    def __rpow__(self, other: t.Any) -> Pow:
 885        return self._binop(Pow, other, reverse=True)
 886
 887    def __and__(self, other: t.Any) -> And:
 888        return self._binop(And, other)
 889
 890    def __rand__(self, other: t.Any) -> And:
 891        return self._binop(And, other, reverse=True)
 892
 893    def __or__(self, other: t.Any) -> Or:
 894        return self._binop(Or, other)
 895
 896    def __ror__(self, other: t.Any) -> Or:
 897        return self._binop(Or, other, reverse=True)
 898
 899    def __neg__(self) -> Neg:
 900        return Neg(this=_wrap(self.copy(), Binary))
 901
 902    def __invert__(self) -> Not:
 903        return not_(self.copy())
 904
 905
 906IntoType = t.Union[
 907    str,
 908    t.Type[Expression],
 909    t.Collection[t.Union[str, t.Type[Expression]]],
 910]
 911ExpOrStr = t.Union[str, Expression]
 912
 913
 914class Condition(Expression):
 915    """Logical conditions like x AND y, or simply x"""
 916
 917
 918class Predicate(Condition):
 919    """Relationships like x = y, x > 1, x >= y."""
 920
 921
 922class DerivedTable(Expression):
 923    @property
 924    def selects(self) -> t.List[Expression]:
 925        return self.this.selects if isinstance(self.this, Subqueryable) else []
 926
 927    @property
 928    def named_selects(self) -> t.List[str]:
 929        return [select.output_name for select in self.selects]
 930
 931
 932class Unionable(Expression):
 933    def union(
 934        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 935    ) -> Unionable:
 936        """
 937        Builds a UNION expression.
 938
 939        Example:
 940            >>> import sqlglot
 941            >>> sqlglot.parse_one("SELECT * FROM foo").union("SELECT * FROM bla").sql()
 942            'SELECT * FROM foo UNION SELECT * FROM bla'
 943
 944        Args:
 945            expression: the SQL code string.
 946                If an `Expression` instance is passed, it will be used as-is.
 947            distinct: set the DISTINCT flag if and only if this is true.
 948            dialect: the dialect used to parse the input expression.
 949            opts: other options to use to parse the input expressions.
 950
 951        Returns:
 952            The new Union expression.
 953        """
 954        return union(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 955
 956    def intersect(
 957        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 958    ) -> Unionable:
 959        """
 960        Builds an INTERSECT expression.
 961
 962        Example:
 963            >>> import sqlglot
 964            >>> sqlglot.parse_one("SELECT * FROM foo").intersect("SELECT * FROM bla").sql()
 965            'SELECT * FROM foo INTERSECT SELECT * FROM bla'
 966
 967        Args:
 968            expression: the SQL code string.
 969                If an `Expression` instance is passed, it will be used as-is.
 970            distinct: set the DISTINCT flag if and only if this is true.
 971            dialect: the dialect used to parse the input expression.
 972            opts: other options to use to parse the input expressions.
 973
 974        Returns:
 975            The new Intersect expression.
 976        """
 977        return intersect(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
 978
 979    def except_(
 980        self, expression: ExpOrStr, distinct: bool = True, dialect: DialectType = None, **opts
 981    ) -> Unionable:
 982        """
 983        Builds an EXCEPT expression.
 984
 985        Example:
 986            >>> import sqlglot
 987            >>> sqlglot.parse_one("SELECT * FROM foo").except_("SELECT * FROM bla").sql()
 988            'SELECT * FROM foo EXCEPT SELECT * FROM bla'
 989
 990        Args:
 991            expression: the SQL code string.
 992                If an `Expression` instance is passed, it will be used as-is.
 993            distinct: set the DISTINCT flag if and only if this is true.
 994            dialect: the dialect used to parse the input expression.
 995            opts: other options to use to parse the input expressions.
 996
 997        Returns:
 998            The new Except expression.
 999        """
1000        return except_(left=self, right=expression, distinct=distinct, dialect=dialect, **opts)
1001
1002
1003class UDTF(DerivedTable, Unionable):
1004    @property
1005    def selects(self) -> t.List[Expression]:
1006        alias = self.args.get("alias")
1007        return alias.columns if alias else []
1008
1009
1010class Cache(Expression):
1011    arg_types = {
1012        "this": True,
1013        "lazy": False,
1014        "options": False,
1015        "expression": False,
1016    }
1017
1018
1019class Uncache(Expression):
1020    arg_types = {"this": True, "exists": False}
1021
1022
1023class Refresh(Expression):
1024    pass
1025
1026
1027class DDL(Expression):
1028    @property
1029    def ctes(self):
1030        with_ = self.args.get("with")
1031        if not with_:
1032            return []
1033        return with_.expressions
1034
1035    @property
1036    def named_selects(self) -> t.List[str]:
1037        if isinstance(self.expression, Subqueryable):
1038            return self.expression.named_selects
1039        return []
1040
1041    @property
1042    def selects(self) -> t.List[Expression]:
1043        if isinstance(self.expression, Subqueryable):
1044            return self.expression.selects
1045        return []
1046
1047
1048class Create(DDL):
1049    arg_types = {
1050        "with": False,
1051        "this": True,
1052        "kind": True,
1053        "expression": False,
1054        "exists": False,
1055        "properties": False,
1056        "replace": False,
1057        "unique": False,
1058        "indexes": False,
1059        "no_schema_binding": False,
1060        "begin": False,
1061        "end": False,
1062        "clone": False,
1063    }
1064
1065
1066# https://docs.snowflake.com/en/sql-reference/sql/create-clone
1067# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_clone_statement
1068# https://cloud.google.com/bigquery/docs/reference/standard-sql/data-definition-language#create_table_copy
1069class Clone(Expression):
1070    arg_types = {
1071        "this": True,
1072        "when": False,
1073        "kind": False,
1074        "shallow": False,
1075        "expression": False,
1076        "copy": False,
1077    }
1078
1079
1080class Describe(Expression):
1081    arg_types = {"this": True, "kind": False, "expressions": False}
1082
1083
1084class Kill(Expression):
1085    arg_types = {"this": True, "kind": False}
1086
1087
1088class Pragma(Expression):
1089    pass
1090
1091
1092class Set(Expression):
1093    arg_types = {"expressions": False, "unset": False, "tag": False}
1094
1095
1096class SetItem(Expression):
1097    arg_types = {
1098        "this": False,
1099        "expressions": False,
1100        "kind": False,
1101        "collate": False,  # MySQL SET NAMES statement
1102        "global": False,
1103    }
1104
1105
1106class Show(Expression):
1107    arg_types = {
1108        "this": True,
1109        "target": False,
1110        "offset": False,
1111        "limit": False,
1112        "like": False,
1113        "where": False,
1114        "db": False,
1115        "scope": False,
1116        "scope_kind": False,
1117        "full": False,
1118        "mutex": False,
1119        "query": False,
1120        "channel": False,
1121        "global": False,
1122        "log": False,
1123        "position": False,
1124        "types": False,
1125    }
1126
1127
1128class UserDefinedFunction(Expression):
1129    arg_types = {"this": True, "expressions": False, "wrapped": False}
1130
1131
1132class CharacterSet(Expression):
1133    arg_types = {"this": True, "default": False}
1134
1135
1136class With(Expression):
1137    arg_types = {"expressions": True, "recursive": False}
1138
1139    @property
1140    def recursive(self) -> bool:
1141        return bool(self.args.get("recursive"))
1142
1143
1144class WithinGroup(Expression):
1145    arg_types = {"this": True, "expression": False}
1146
1147
1148# clickhouse supports scalar ctes
1149# https://clickhouse.com/docs/en/sql-reference/statements/select/with
1150class CTE(DerivedTable):
1151    arg_types = {"this": True, "alias": True, "scalar": False}
1152
1153
1154class TableAlias(Expression):
1155    arg_types = {"this": False, "columns": False}
1156
1157    @property
1158    def columns(self):
1159        return self.args.get("columns") or []
1160
1161
1162class BitString(Condition):
1163    pass
1164
1165
1166class HexString(Condition):
1167    pass
1168
1169
1170class ByteString(Condition):
1171    pass
1172
1173
1174class RawString(Condition):
1175    pass
1176
1177
1178class Column(Condition):
1179    arg_types = {"this": True, "table": False, "db": False, "catalog": False, "join_mark": False}
1180
1181    @property
1182    def table(self) -> str:
1183        return self.text("table")
1184
1185    @property
1186    def db(self) -> str:
1187        return self.text("db")
1188
1189    @property
1190    def catalog(self) -> str:
1191        return self.text("catalog")
1192
1193    @property
1194    def output_name(self) -> str:
1195        return self.name
1196
1197    @property
1198    def parts(self) -> t.List[Identifier]:
1199        """Return the parts of a column in order catalog, db, table, name."""
1200        return [
1201            t.cast(Identifier, self.args[part])
1202            for part in ("catalog", "db", "table", "this")
1203            if self.args.get(part)
1204        ]
1205
1206    def to_dot(self) -> Dot | Identifier:
1207        """Converts the column into a dot expression."""
1208        parts = self.parts
1209        parent = self.parent
1210
1211        while parent:
1212            if isinstance(parent, Dot):
1213                parts.append(parent.expression)
1214            parent = parent.parent
1215
1216        return Dot.build(deepcopy(parts)) if len(parts) > 1 else parts[0]
1217
1218
1219class ColumnPosition(Expression):
1220    arg_types = {"this": False, "position": True}
1221
1222
1223class ColumnDef(Expression):
1224    arg_types = {
1225        "this": True,
1226        "kind": False,
1227        "constraints": False,
1228        "exists": False,
1229        "position": False,
1230    }
1231
1232    @property
1233    def constraints(self) -> t.List[ColumnConstraint]:
1234        return self.args.get("constraints") or []
1235
1236
1237class AlterColumn(Expression):
1238    arg_types = {
1239        "this": True,
1240        "dtype": False,
1241        "collate": False,
1242        "using": False,
1243        "default": False,
1244        "drop": False,
1245    }
1246
1247
1248class RenameTable(Expression):
1249    pass
1250
1251
1252class SwapTable(Expression):
1253    pass
1254
1255
1256class Comment(Expression):
1257    arg_types = {"this": True, "kind": True, "expression": True, "exists": False}
1258
1259
1260class Comprehension(Expression):
1261    arg_types = {"this": True, "expression": True, "iterator": True, "condition": False}
1262
1263
1264# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1265class MergeTreeTTLAction(Expression):
1266    arg_types = {
1267        "this": True,
1268        "delete": False,
1269        "recompress": False,
1270        "to_disk": False,
1271        "to_volume": False,
1272    }
1273
1274
1275# https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree#mergetree-table-ttl
1276class MergeTreeTTL(Expression):
1277    arg_types = {
1278        "expressions": True,
1279        "where": False,
1280        "group": False,
1281        "aggregates": False,
1282    }
1283
1284
1285# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1286class IndexConstraintOption(Expression):
1287    arg_types = {
1288        "key_block_size": False,
1289        "using": False,
1290        "parser": False,
1291        "comment": False,
1292        "visible": False,
1293        "engine_attr": False,
1294        "secondary_engine_attr": False,
1295    }
1296
1297
1298class ColumnConstraint(Expression):
1299    arg_types = {"this": False, "kind": True}
1300
1301    @property
1302    def kind(self) -> ColumnConstraintKind:
1303        return self.args["kind"]
1304
1305
1306class ColumnConstraintKind(Expression):
1307    pass
1308
1309
1310class AutoIncrementColumnConstraint(ColumnConstraintKind):
1311    pass
1312
1313
1314class PeriodForSystemTimeConstraint(ColumnConstraintKind):
1315    arg_types = {"this": True, "expression": True}
1316
1317
1318class CaseSpecificColumnConstraint(ColumnConstraintKind):
1319    arg_types = {"not_": True}
1320
1321
1322class CharacterSetColumnConstraint(ColumnConstraintKind):
1323    arg_types = {"this": True}
1324
1325
1326class CheckColumnConstraint(ColumnConstraintKind):
1327    pass
1328
1329
1330class ClusteredColumnConstraint(ColumnConstraintKind):
1331    pass
1332
1333
1334class CollateColumnConstraint(ColumnConstraintKind):
1335    pass
1336
1337
1338class CommentColumnConstraint(ColumnConstraintKind):
1339    pass
1340
1341
1342class CompressColumnConstraint(ColumnConstraintKind):
1343    pass
1344
1345
1346class DateFormatColumnConstraint(ColumnConstraintKind):
1347    arg_types = {"this": True}
1348
1349
1350class DefaultColumnConstraint(ColumnConstraintKind):
1351    pass
1352
1353
1354class EncodeColumnConstraint(ColumnConstraintKind):
1355    pass
1356
1357
1358class GeneratedAsIdentityColumnConstraint(ColumnConstraintKind):
1359    # this: True -> ALWAYS, this: False -> BY DEFAULT
1360    arg_types = {
1361        "this": False,
1362        "expression": False,
1363        "on_null": False,
1364        "start": False,
1365        "increment": False,
1366        "minvalue": False,
1367        "maxvalue": False,
1368        "cycle": False,
1369    }
1370
1371
1372class GeneratedAsRowColumnConstraint(ColumnConstraintKind):
1373    arg_types = {"start": True, "hidden": False}
1374
1375
1376# https://dev.mysql.com/doc/refman/8.0/en/create-table.html
1377class IndexColumnConstraint(ColumnConstraintKind):
1378    arg_types = {
1379        "this": False,
1380        "schema": True,
1381        "kind": False,
1382        "index_type": False,
1383        "options": False,
1384    }
1385
1386
1387class InlineLengthColumnConstraint(ColumnConstraintKind):
1388    pass
1389
1390
1391class NonClusteredColumnConstraint(ColumnConstraintKind):
1392    pass
1393
1394
1395class NotForReplicationColumnConstraint(ColumnConstraintKind):
1396    arg_types = {}
1397
1398
1399class NotNullColumnConstraint(ColumnConstraintKind):
1400    arg_types = {"allow_null": False}
1401
1402
1403# https://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html
1404class OnUpdateColumnConstraint(ColumnConstraintKind):
1405    pass
1406
1407
1408class PrimaryKeyColumnConstraint(ColumnConstraintKind):
1409    arg_types = {"desc": False}
1410
1411
1412class TitleColumnConstraint(ColumnConstraintKind):
1413    pass
1414
1415
1416class UniqueColumnConstraint(ColumnConstraintKind):
1417    arg_types = {"this": False, "index_type": False}
1418
1419
1420class UppercaseColumnConstraint(ColumnConstraintKind):
1421    arg_types: t.Dict[str, t.Any] = {}
1422
1423
1424class PathColumnConstraint(ColumnConstraintKind):
1425    pass
1426
1427
1428# computed column expression
1429# https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver16
1430class ComputedColumnConstraint(ColumnConstraintKind):
1431    arg_types = {"this": True, "persisted": False, "not_null": False}
1432
1433
1434class Constraint(Expression):
1435    arg_types = {"this": True, "expressions": True}
1436
1437
1438class Delete(Expression):
1439    arg_types = {
1440        "with": False,
1441        "this": False,
1442        "using": False,
1443        "where": False,
1444        "returning": False,
1445        "limit": False,
1446        "tables": False,  # Multiple-Table Syntax (MySQL)
1447    }
1448
1449    def delete(
1450        self,
1451        table: ExpOrStr,
1452        dialect: DialectType = None,
1453        copy: bool = True,
1454        **opts,
1455    ) -> Delete:
1456        """
1457        Create a DELETE expression or replace the table on an existing DELETE expression.
1458
1459        Example:
1460            >>> delete("tbl").sql()
1461            'DELETE FROM tbl'
1462
1463        Args:
1464            table: the table from which to delete.
1465            dialect: the dialect used to parse the input expression.
1466            copy: if `False`, modify this expression instance in-place.
1467            opts: other options to use to parse the input expressions.
1468
1469        Returns:
1470            Delete: the modified expression.
1471        """
1472        return _apply_builder(
1473            expression=table,
1474            instance=self,
1475            arg="this",
1476            dialect=dialect,
1477            into=Table,
1478            copy=copy,
1479            **opts,
1480        )
1481
1482    def where(
1483        self,
1484        *expressions: t.Optional[ExpOrStr],
1485        append: bool = True,
1486        dialect: DialectType = None,
1487        copy: bool = True,
1488        **opts,
1489    ) -> Delete:
1490        """
1491        Append to or set the WHERE expressions.
1492
1493        Example:
1494            >>> delete("tbl").where("x = 'a' OR x < 'b'").sql()
1495            "DELETE FROM tbl WHERE x = 'a' OR x < 'b'"
1496
1497        Args:
1498            *expressions: the SQL code strings to parse.
1499                If an `Expression` instance is passed, it will be used as-is.
1500                Multiple expressions are combined with an AND operator.
1501            append: if `True`, AND the new expressions to any existing expression.
1502                Otherwise, this resets the expression.
1503            dialect: the dialect used to parse the input expressions.
1504            copy: if `False`, modify this expression instance in-place.
1505            opts: other options to use to parse the input expressions.
1506
1507        Returns:
1508            Delete: the modified expression.
1509        """
1510        return _apply_conjunction_builder(
1511            *expressions,
1512            instance=self,
1513            arg="where",
1514            append=append,
1515            into=Where,
1516            dialect=dialect,
1517            copy=copy,
1518            **opts,
1519        )
1520
1521    def returning(
1522        self,
1523        expression: ExpOrStr,
1524        dialect: DialectType = None,
1525        copy: bool = True,
1526        **opts,
1527    ) -> Delete:
1528        """
1529        Set the RETURNING expression. Not supported by all dialects.
1530
1531        Example:
1532            >>> delete("tbl").returning("*", dialect="postgres").sql()
1533            'DELETE FROM tbl RETURNING *'
1534
1535        Args:
1536            expression: the SQL code strings to parse.
1537                If an `Expression` instance is passed, it will be used as-is.
1538            dialect: the dialect used to parse the input expressions.
1539            copy: if `False`, modify this expression instance in-place.
1540            opts: other options to use to parse the input expressions.
1541
1542        Returns:
1543            Delete: the modified expression.
1544        """
1545        return _apply_builder(
1546            expression=expression,
1547            instance=self,
1548            arg="returning",
1549            prefix="RETURNING",
1550            dialect=dialect,
1551            copy=copy,
1552            into=Returning,
1553            **opts,
1554        )
1555
1556
1557class Drop(Expression):
1558    arg_types = {
1559        "this": False,
1560        "kind": False,
1561        "exists": False,
1562        "temporary": False,
1563        "materialized": False,
1564        "cascade": False,
1565        "constraints": False,
1566        "purge": False,
1567    }
1568
1569
1570class Filter(Expression):
1571    arg_types = {"this": True, "expression": True}
1572
1573
1574class Check(Expression):
1575    pass
1576
1577
1578# https://docs.snowflake.com/en/sql-reference/constructs/connect-by
1579class Connect(Expression):
1580    arg_types = {"start": False, "connect": True}
1581
1582
1583class Prior(Expression):
1584    pass
1585
1586
1587class Directory(Expression):
1588    # https://spark.apache.org/docs/3.0.0-preview/sql-ref-syntax-dml-insert-overwrite-directory-hive.html
1589    arg_types = {"this": True, "local": False, "row_format": False}
1590
1591
1592class ForeignKey(Expression):
1593    arg_types = {
1594        "expressions": True,
1595        "reference": False,
1596        "delete": False,
1597        "update": False,
1598    }
1599
1600
1601class ColumnPrefix(Expression):
1602    arg_types = {"this": True, "expression": True}
1603
1604
1605class PrimaryKey(Expression):
1606    arg_types = {"expressions": True, "options": False}
1607
1608
1609# https://www.postgresql.org/docs/9.1/sql-selectinto.html
1610# https://docs.aws.amazon.com/redshift/latest/dg/r_SELECT_INTO.html#r_SELECT_INTO-examples
1611class Into(Expression):
1612    arg_types = {"this": True, "temporary": False, "unlogged": False}
1613
1614
1615class From(Expression):
1616    @property
1617    def name(self) -> str:
1618        return self.this.name
1619
1620    @property
1621    def alias_or_name(self) -> str:
1622        return self.this.alias_or_name
1623
1624
1625class Having(Expression):
1626    pass
1627
1628
1629class Hint(Expression):
1630    arg_types = {"expressions": True}
1631
1632
1633class JoinHint(Expression):
1634    arg_types = {"this": True, "expressions": True}
1635
1636
1637class Identifier(Expression):
1638    arg_types = {"this": True, "quoted": False, "global": False, "temporary": False}
1639
1640    @property
1641    def quoted(self) -> bool:
1642        return bool(self.args.get("quoted"))
1643
1644    @property
1645    def hashable_args(self) -> t.Any:
1646        return (self.this, self.quoted)
1647
1648    @property
1649    def output_name(self) -> str:
1650        return self.name
1651
1652
1653# https://www.postgresql.org/docs/current/indexes-opclass.html
1654class Opclass(Expression):
1655    arg_types = {"this": True, "expression": True}
1656
1657
1658class Index(Expression):
1659    arg_types = {
1660        "this": False,
1661        "table": False,
1662        "using": False,
1663        "where": False,
1664        "columns": False,
1665        "unique": False,
1666        "primary": False,
1667        "amp": False,  # teradata
1668        "partition_by": False,  # teradata
1669        "where": False,  # postgres partial indexes
1670    }
1671
1672
1673class Insert(DDL):
1674    arg_types = {
1675        "with": False,
1676        "this": True,
1677        "expression": False,
1678        "conflict": False,
1679        "returning": False,
1680        "overwrite": False,
1681        "exists": False,
1682        "partition": False,
1683        "alternative": False,
1684        "where": False,
1685        "ignore": False,
1686        "by_name": False,
1687    }
1688
1689    def with_(
1690        self,
1691        alias: ExpOrStr,
1692        as_: ExpOrStr,
1693        recursive: t.Optional[bool] = None,
1694        append: bool = True,
1695        dialect: DialectType = None,
1696        copy: bool = True,
1697        **opts,
1698    ) -> Insert:
1699        """
1700        Append to or set the common table expressions.
1701
1702        Example:
1703            >>> insert("SELECT x FROM cte", "t").with_("cte", as_="SELECT * FROM tbl").sql()
1704            'WITH cte AS (SELECT * FROM tbl) INSERT INTO t SELECT x FROM cte'
1705
1706        Args:
1707            alias: the SQL code string to parse as the table name.
1708                If an `Expression` instance is passed, this is used as-is.
1709            as_: the SQL code string to parse as the table expression.
1710                If an `Expression` instance is passed, it will be used as-is.
1711            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
1712            append: if `True`, add to any existing expressions.
1713                Otherwise, this resets the expressions.
1714            dialect: the dialect used to parse the input expression.
1715            copy: if `False`, modify this expression instance in-place.
1716            opts: other options to use to parse the input expressions.
1717
1718        Returns:
1719            The modified expression.
1720        """
1721        return _apply_cte_builder(
1722            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
1723        )
1724
1725
1726class OnConflict(Expression):
1727    arg_types = {
1728        "duplicate": False,
1729        "expressions": False,
1730        "nothing": False,
1731        "key": False,
1732        "constraint": False,
1733    }
1734
1735
1736class Returning(Expression):
1737    arg_types = {"expressions": True, "into": False}
1738
1739
1740# https://dev.mysql.com/doc/refman/8.0/en/charset-introducer.html
1741class Introducer(Expression):
1742    arg_types = {"this": True, "expression": True}
1743
1744
1745# national char, like n'utf8'
1746class National(Expression):
1747    pass
1748
1749
1750class LoadData(Expression):
1751    arg_types = {
1752        "this": True,
1753        "local": False,
1754        "overwrite": False,
1755        "inpath": True,
1756        "partition": False,
1757        "input_format": False,
1758        "serde": False,
1759    }
1760
1761
1762class Partition(Expression):
1763    arg_types = {"expressions": True}
1764
1765
1766class Fetch(Expression):
1767    arg_types = {
1768        "direction": False,
1769        "count": False,
1770        "percent": False,
1771        "with_ties": False,
1772    }
1773
1774
1775class Group(Expression):
1776    arg_types = {
1777        "expressions": False,
1778        "grouping_sets": False,
1779        "cube": False,
1780        "rollup": False,
1781        "totals": False,
1782        "all": False,
1783    }
1784
1785
1786class Lambda(Expression):
1787    arg_types = {"this": True, "expressions": True}
1788
1789
1790class Limit(Expression):
1791    arg_types = {"this": False, "expression": True, "offset": False}
1792
1793
1794class Literal(Condition):
1795    arg_types = {"this": True, "is_string": True}
1796
1797    @property
1798    def hashable_args(self) -> t.Any:
1799        return (self.this, self.args.get("is_string"))
1800
1801    @classmethod
1802    def number(cls, number) -> Literal:
1803        return cls(this=str(number), is_string=False)
1804
1805    @classmethod
1806    def string(cls, string) -> Literal:
1807        return cls(this=str(string), is_string=True)
1808
1809    @property
1810    def output_name(self) -> str:
1811        return self.name
1812
1813
1814class Join(Expression):
1815    arg_types = {
1816        "this": True,
1817        "on": False,
1818        "side": False,
1819        "kind": False,
1820        "using": False,
1821        "method": False,
1822        "global": False,
1823        "hint": False,
1824    }
1825
1826    @property
1827    def method(self) -> str:
1828        return self.text("method").upper()
1829
1830    @property
1831    def kind(self) -> str:
1832        return self.text("kind").upper()
1833
1834    @property
1835    def side(self) -> str:
1836        return self.text("side").upper()
1837
1838    @property
1839    def hint(self) -> str:
1840        return self.text("hint").upper()
1841
1842    @property
1843    def alias_or_name(self) -> str:
1844        return self.this.alias_or_name
1845
1846    def on(
1847        self,
1848        *expressions: t.Optional[ExpOrStr],
1849        append: bool = True,
1850        dialect: DialectType = None,
1851        copy: bool = True,
1852        **opts,
1853    ) -> Join:
1854        """
1855        Append to or set the ON expressions.
1856
1857        Example:
1858            >>> import sqlglot
1859            >>> sqlglot.parse_one("JOIN x", into=Join).on("y = 1").sql()
1860            'JOIN x ON y = 1'
1861
1862        Args:
1863            *expressions: the SQL code strings to parse.
1864                If an `Expression` instance is passed, it will be used as-is.
1865                Multiple expressions are combined with an AND operator.
1866            append: if `True`, AND the new expressions to any existing expression.
1867                Otherwise, this resets the expression.
1868            dialect: the dialect used to parse the input expressions.
1869            copy: if `False`, modify this expression instance in-place.
1870            opts: other options to use to parse the input expressions.
1871
1872        Returns:
1873            The modified Join expression.
1874        """
1875        join = _apply_conjunction_builder(
1876            *expressions,
1877            instance=self,
1878            arg="on",
1879            append=append,
1880            dialect=dialect,
1881            copy=copy,
1882            **opts,
1883        )
1884
1885        if join.kind == "CROSS":
1886            join.set("kind", None)
1887
1888        return join
1889
1890    def using(
1891        self,
1892        *expressions: t.Optional[ExpOrStr],
1893        append: bool = True,
1894        dialect: DialectType = None,
1895        copy: bool = True,
1896        **opts,
1897    ) -> Join:
1898        """
1899        Append to or set the USING expressions.
1900
1901        Example:
1902            >>> import sqlglot
1903            >>> sqlglot.parse_one("JOIN x", into=Join).using("foo", "bla").sql()
1904            'JOIN x USING (foo, bla)'
1905
1906        Args:
1907            *expressions: the SQL code strings to parse.
1908                If an `Expression` instance is passed, it will be used as-is.
1909            append: if `True`, concatenate the new expressions to the existing "using" list.
1910                Otherwise, this resets the expression.
1911            dialect: the dialect used to parse the input expressions.
1912            copy: if `False`, modify this expression instance in-place.
1913            opts: other options to use to parse the input expressions.
1914
1915        Returns:
1916            The modified Join expression.
1917        """
1918        join = _apply_list_builder(
1919            *expressions,
1920            instance=self,
1921            arg="using",
1922            append=append,
1923            dialect=dialect,
1924            copy=copy,
1925            **opts,
1926        )
1927
1928        if join.kind == "CROSS":
1929            join.set("kind", None)
1930
1931        return join
1932
1933
1934class Lateral(UDTF):
1935    arg_types = {"this": True, "view": False, "outer": False, "alias": False}
1936
1937
1938class MatchRecognize(Expression):
1939    arg_types = {
1940        "partition_by": False,
1941        "order": False,
1942        "measures": False,
1943        "rows": False,
1944        "after": False,
1945        "pattern": False,
1946        "define": False,
1947        "alias": False,
1948    }
1949
1950
1951# Clickhouse FROM FINAL modifier
1952# https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier
1953class Final(Expression):
1954    pass
1955
1956
1957class Offset(Expression):
1958    arg_types = {"this": False, "expression": True}
1959
1960
1961class Order(Expression):
1962    arg_types = {"this": False, "expressions": True}
1963
1964
1965# hive specific sorts
1966# https://cwiki.apache.org/confluence/display/Hive/LanguageManual+SortBy
1967class Cluster(Order):
1968    pass
1969
1970
1971class Distribute(Order):
1972    pass
1973
1974
1975class Sort(Order):
1976    pass
1977
1978
1979class Ordered(Expression):
1980    arg_types = {"this": True, "desc": False, "nulls_first": True}
1981
1982
1983class Property(Expression):
1984    arg_types = {"this": True, "value": True}
1985
1986
1987class AlgorithmProperty(Property):
1988    arg_types = {"this": True}
1989
1990
1991class AutoIncrementProperty(Property):
1992    arg_types = {"this": True}
1993
1994
1995class BlockCompressionProperty(Property):
1996    arg_types = {"autotemp": False, "always": False, "default": True, "manual": True, "never": True}
1997
1998
1999class CharacterSetProperty(Property):
2000    arg_types = {"this": True, "default": True}
2001
2002
2003class ChecksumProperty(Property):
2004    arg_types = {"on": False, "default": False}
2005
2006
2007class CollateProperty(Property):
2008    arg_types = {"this": True, "default": False}
2009
2010
2011class CopyGrantsProperty(Property):
2012    arg_types = {}
2013
2014
2015class DataBlocksizeProperty(Property):
2016    arg_types = {
2017        "size": False,
2018        "units": False,
2019        "minimum": False,
2020        "maximum": False,
2021        "default": False,
2022    }
2023
2024
2025class DefinerProperty(Property):
2026    arg_types = {"this": True}
2027
2028
2029class DistKeyProperty(Property):
2030    arg_types = {"this": True}
2031
2032
2033class DistStyleProperty(Property):
2034    arg_types = {"this": True}
2035
2036
2037class EngineProperty(Property):
2038    arg_types = {"this": True}
2039
2040
2041class HeapProperty(Property):
2042    arg_types = {}
2043
2044
2045class ToTableProperty(Property):
2046    arg_types = {"this": True}
2047
2048
2049class ExecuteAsProperty(Property):
2050    arg_types = {"this": True}
2051
2052
2053class ExternalProperty(Property):
2054    arg_types = {"this": False}
2055
2056
2057class FallbackProperty(Property):
2058    arg_types = {"no": True, "protection": False}
2059
2060
2061class FileFormatProperty(Property):
2062    arg_types = {"this": True}
2063
2064
2065class FreespaceProperty(Property):
2066    arg_types = {"this": True, "percent": False}
2067
2068
2069class InputModelProperty(Property):
2070    arg_types = {"this": True}
2071
2072
2073class OutputModelProperty(Property):
2074    arg_types = {"this": True}
2075
2076
2077class IsolatedLoadingProperty(Property):
2078    arg_types = {
2079        "no": True,
2080        "concurrent": True,
2081        "for_all": True,
2082        "for_insert": True,
2083        "for_none": True,
2084    }
2085
2086
2087class JournalProperty(Property):
2088    arg_types = {
2089        "no": False,
2090        "dual": False,
2091        "before": False,
2092        "local": False,
2093        "after": False,
2094    }
2095
2096
2097class LanguageProperty(Property):
2098    arg_types = {"this": True}
2099
2100
2101# spark ddl
2102class ClusteredByProperty(Property):
2103    arg_types = {"expressions": True, "sorted_by": False, "buckets": True}
2104
2105
2106class DictProperty(Property):
2107    arg_types = {"this": True, "kind": True, "settings": False}
2108
2109
2110class DictSubProperty(Property):
2111    pass
2112
2113
2114class DictRange(Property):
2115    arg_types = {"this": True, "min": True, "max": True}
2116
2117
2118# Clickhouse CREATE ... ON CLUSTER modifier
2119# https://clickhouse.com/docs/en/sql-reference/distributed-ddl
2120class OnCluster(Property):
2121    arg_types = {"this": True}
2122
2123
2124class LikeProperty(Property):
2125    arg_types = {"this": True, "expressions": False}
2126
2127
2128class LocationProperty(Property):
2129    arg_types = {"this": True}
2130
2131
2132class LockingProperty(Property):
2133    arg_types = {
2134        "this": False,
2135        "kind": True,
2136        "for_or_in": False,
2137        "lock_type": True,
2138        "override": False,
2139    }
2140
2141
2142class LogProperty(Property):
2143    arg_types = {"no": True}
2144
2145
2146class MaterializedProperty(Property):
2147    arg_types = {"this": False}
2148
2149
2150class MergeBlockRatioProperty(Property):
2151    arg_types = {"this": False, "no": False, "default": False, "percent": False}
2152
2153
2154class NoPrimaryIndexProperty(Property):
2155    arg_types = {}
2156
2157
2158class OnProperty(Property):
2159    arg_types = {"this": True}
2160
2161
2162class OnCommitProperty(Property):
2163    arg_types = {"delete": False}
2164
2165
2166class PartitionedByProperty(Property):
2167    arg_types = {"this": True}
2168
2169
2170# https://www.postgresql.org/docs/current/sql-createtable.html
2171class PartitionBoundSpec(Expression):
2172    # this -> IN / MODULUS, expression -> REMAINDER, from_expressions -> FROM (...), to_expressions -> TO (...)
2173    arg_types = {
2174        "this": False,
2175        "expression": False,
2176        "from_expressions": False,
2177        "to_expressions": False,
2178    }
2179
2180
2181class PartitionedOfProperty(Property):
2182    # this -> parent_table (schema), expression -> FOR VALUES ... / DEFAULT
2183    arg_types = {"this": True, "expression": True}
2184
2185
2186class RemoteWithConnectionModelProperty(Property):
2187    arg_types = {"this": True}
2188
2189
2190class ReturnsProperty(Property):
2191    arg_types = {"this": True, "is_table": False, "table": False}
2192
2193
2194class RowFormatProperty(Property):
2195    arg_types = {"this": True}
2196
2197
2198class RowFormatDelimitedProperty(Property):
2199    # https://cwiki.apache.org/confluence/display/hive/languagemanual+dml
2200    arg_types = {
2201        "fields": False,
2202        "escaped": False,
2203        "collection_items": False,
2204        "map_keys": False,
2205        "lines": False,
2206        "null": False,
2207        "serde": False,
2208    }
2209
2210
2211class RowFormatSerdeProperty(Property):
2212    arg_types = {"this": True, "serde_properties": False}
2213
2214
2215# https://spark.apache.org/docs/3.1.2/sql-ref-syntax-qry-select-transform.html
2216class QueryTransform(Expression):
2217    arg_types = {
2218        "expressions": True,
2219        "command_script": True,
2220        "schema": False,
2221        "row_format_before": False,
2222        "record_writer": False,
2223        "row_format_after": False,
2224        "record_reader": False,
2225    }
2226
2227
2228class SampleProperty(Property):
2229    arg_types = {"this": True}
2230
2231
2232class SchemaCommentProperty(Property):
2233    arg_types = {"this": True}
2234
2235
2236class SerdeProperties(Property):
2237    arg_types = {"expressions": True}
2238
2239
2240class SetProperty(Property):
2241    arg_types = {"multi": True}
2242
2243
2244class SettingsProperty(Property):
2245    arg_types = {"expressions": True}
2246
2247
2248class SortKeyProperty(Property):
2249    arg_types = {"this": True, "compound": False}
2250
2251
2252class SqlSecurityProperty(Property):
2253    arg_types = {"definer": True}
2254
2255
2256class StabilityProperty(Property):
2257    arg_types = {"this": True}
2258
2259
2260class TemporaryProperty(Property):
2261    arg_types = {}
2262
2263
2264class TransformModelProperty(Property):
2265    arg_types = {"expressions": True}
2266
2267
2268class TransientProperty(Property):
2269    arg_types = {"this": False}
2270
2271
2272class VolatileProperty(Property):
2273    arg_types = {"this": False}
2274
2275
2276class WithDataProperty(Property):
2277    arg_types = {"no": True, "statistics": False}
2278
2279
2280class WithJournalTableProperty(Property):
2281    arg_types = {"this": True}
2282
2283
2284class WithSystemVersioningProperty(Property):
2285    # this -> history table name, expression -> data consistency check
2286    arg_types = {"this": False, "expression": False}
2287
2288
2289class Properties(Expression):
2290    arg_types = {"expressions": True}
2291
2292    NAME_TO_PROPERTY = {
2293        "ALGORITHM": AlgorithmProperty,
2294        "AUTO_INCREMENT": AutoIncrementProperty,
2295        "CHARACTER SET": CharacterSetProperty,
2296        "CLUSTERED_BY": ClusteredByProperty,
2297        "COLLATE": CollateProperty,
2298        "COMMENT": SchemaCommentProperty,
2299        "DEFINER": DefinerProperty,
2300        "DISTKEY": DistKeyProperty,
2301        "DISTSTYLE": DistStyleProperty,
2302        "ENGINE": EngineProperty,
2303        "EXECUTE AS": ExecuteAsProperty,
2304        "FORMAT": FileFormatProperty,
2305        "LANGUAGE": LanguageProperty,
2306        "LOCATION": LocationProperty,
2307        "PARTITIONED_BY": PartitionedByProperty,
2308        "RETURNS": ReturnsProperty,
2309        "ROW_FORMAT": RowFormatProperty,
2310        "SORTKEY": SortKeyProperty,
2311    }
2312
2313    PROPERTY_TO_NAME = {v: k for k, v in NAME_TO_PROPERTY.items()}
2314
2315    # CREATE property locations
2316    # Form: schema specified
2317    #   create [POST_CREATE]
2318    #     table a [POST_NAME]
2319    #     (b int) [POST_SCHEMA]
2320    #     with ([POST_WITH])
2321    #     index (b) [POST_INDEX]
2322    #
2323    # Form: alias selection
2324    #   create [POST_CREATE]
2325    #     table a [POST_NAME]
2326    #     as [POST_ALIAS] (select * from b) [POST_EXPRESSION]
2327    #     index (c) [POST_INDEX]
2328    class Location(AutoName):
2329        POST_CREATE = auto()
2330        POST_NAME = auto()
2331        POST_SCHEMA = auto()
2332        POST_WITH = auto()
2333        POST_ALIAS = auto()
2334        POST_EXPRESSION = auto()
2335        POST_INDEX = auto()
2336        UNSUPPORTED = auto()
2337
2338    @classmethod
2339    def from_dict(cls, properties_dict: t.Dict) -> Properties:
2340        expressions = []
2341        for key, value in properties_dict.items():
2342            property_cls = cls.NAME_TO_PROPERTY.get(key.upper())
2343            if property_cls:
2344                expressions.append(property_cls(this=convert(value)))
2345            else:
2346                expressions.append(Property(this=Literal.string(key), value=convert(value)))
2347
2348        return cls(expressions=expressions)
2349
2350
2351class Qualify(Expression):
2352    pass
2353
2354
2355class InputOutputFormat(Expression):
2356    arg_types = {"input_format": False, "output_format": False}
2357
2358
2359# https://www.ibm.com/docs/en/ias?topic=procedures-return-statement-in-sql
2360class Return(Expression):
2361    pass
2362
2363
2364class Reference(Expression):
2365    arg_types = {"this": True, "expressions": False, "options": False}
2366
2367
2368class Tuple(Expression):
2369    arg_types = {"expressions": False}
2370
2371    def isin(
2372        self,
2373        *expressions: t.Any,
2374        query: t.Optional[ExpOrStr] = None,
2375        unnest: t.Optional[ExpOrStr] | t.Collection[ExpOrStr] = None,
2376        copy: bool = True,
2377        **opts,
2378    ) -> In:
2379        return In(
2380            this=maybe_copy(self, copy),
2381            expressions=[convert(e, copy=copy) for e in expressions],
2382            query=maybe_parse(query, copy=copy, **opts) if query else None,
2383            unnest=Unnest(
2384                expressions=[
2385                    maybe_parse(t.cast(ExpOrStr, e), copy=copy, **opts) for e in ensure_list(unnest)
2386                ]
2387            )
2388            if unnest
2389            else None,
2390        )
2391
2392
2393class Subqueryable(Unionable):
2394    def subquery(self, alias: t.Optional[ExpOrStr] = None, copy: bool = True) -> Subquery:
2395        """
2396        Convert this expression to an aliased expression that can be used as a Subquery.
2397
2398        Example:
2399            >>> subquery = Select().select("x").from_("tbl").subquery()
2400            >>> Select().select("x").from_(subquery).sql()
2401            'SELECT x FROM (SELECT x FROM tbl)'
2402
2403        Args:
2404            alias (str | Identifier): an optional alias for the subquery
2405            copy (bool): if `False`, modify this expression instance in-place.
2406
2407        Returns:
2408            Alias: the subquery
2409        """
2410        instance = maybe_copy(self, copy)
2411        if not isinstance(alias, Expression):
2412            alias = TableAlias(this=to_identifier(alias)) if alias else None
2413
2414        return Subquery(this=instance, alias=alias)
2415
2416    def limit(
2417        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2418    ) -> Select:
2419        raise NotImplementedError
2420
2421    @property
2422    def ctes(self):
2423        with_ = self.args.get("with")
2424        if not with_:
2425            return []
2426        return with_.expressions
2427
2428    @property
2429    def selects(self) -> t.List[Expression]:
2430        raise NotImplementedError("Subqueryable objects must implement `selects`")
2431
2432    @property
2433    def named_selects(self) -> t.List[str]:
2434        raise NotImplementedError("Subqueryable objects must implement `named_selects`")
2435
2436    def select(
2437        self,
2438        *expressions: t.Optional[ExpOrStr],
2439        append: bool = True,
2440        dialect: DialectType = None,
2441        copy: bool = True,
2442        **opts,
2443    ) -> Subqueryable:
2444        raise NotImplementedError("Subqueryable objects must implement `select`")
2445
2446    def with_(
2447        self,
2448        alias: ExpOrStr,
2449        as_: ExpOrStr,
2450        recursive: t.Optional[bool] = None,
2451        append: bool = True,
2452        dialect: DialectType = None,
2453        copy: bool = True,
2454        **opts,
2455    ) -> Subqueryable:
2456        """
2457        Append to or set the common table expressions.
2458
2459        Example:
2460            >>> Select().with_("tbl2", as_="SELECT * FROM tbl").select("x").from_("tbl2").sql()
2461            'WITH tbl2 AS (SELECT * FROM tbl) SELECT x FROM tbl2'
2462
2463        Args:
2464            alias: the SQL code string to parse as the table name.
2465                If an `Expression` instance is passed, this is used as-is.
2466            as_: the SQL code string to parse as the table expression.
2467                If an `Expression` instance is passed, it will be used as-is.
2468            recursive: set the RECURSIVE part of the expression. Defaults to `False`.
2469            append: if `True`, add to any existing expressions.
2470                Otherwise, this resets the expressions.
2471            dialect: the dialect used to parse the input expression.
2472            copy: if `False`, modify this expression instance in-place.
2473            opts: other options to use to parse the input expressions.
2474
2475        Returns:
2476            The modified expression.
2477        """
2478        return _apply_cte_builder(
2479            self, alias, as_, recursive=recursive, append=append, dialect=dialect, copy=copy, **opts
2480        )
2481
2482
2483QUERY_MODIFIERS = {
2484    "match": False,
2485    "laterals": False,
2486    "joins": False,
2487    "connect": False,
2488    "pivots": False,
2489    "where": False,
2490    "group": False,
2491    "having": False,
2492    "qualify": False,
2493    "windows": False,
2494    "distribute": False,
2495    "sort": False,
2496    "cluster": False,
2497    "order": False,
2498    "limit": False,
2499    "offset": False,
2500    "locks": False,
2501    "sample": False,
2502    "settings": False,
2503    "format": False,
2504}
2505
2506
2507# https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver16
2508class WithTableHint(Expression):
2509    arg_types = {"expressions": True}
2510
2511
2512# https://dev.mysql.com/doc/refman/8.0/en/index-hints.html
2513class IndexTableHint(Expression):
2514    arg_types = {"this": True, "expressions": False, "target": False}
2515
2516
2517class Table(Expression):
2518    arg_types = {
2519        "this": True,
2520        "alias": False,
2521        "db": False,
2522        "catalog": False,
2523        "laterals": False,
2524        "joins": False,
2525        "pivots": False,
2526        "hints": False,
2527        "system_time": False,
2528        "version": False,
2529        "format": False,
2530        "pattern": False,
2531        "index": False,
2532        "ordinality": False,
2533    }
2534
2535    @property
2536    def name(self) -> str:
2537        if isinstance(self.this, Func):
2538            return ""
2539        return self.this.name
2540
2541    @property
2542    def db(self) -> str:
2543        return self.text("db")
2544
2545    @property
2546    def catalog(self) -> str:
2547        return self.text("catalog")
2548
2549    @property
2550    def selects(self) -> t.List[Expression]:
2551        return []
2552
2553    @property
2554    def named_selects(self) -> t.List[str]:
2555        return []
2556
2557    @property
2558    def parts(self) -> t.List[Expression]:
2559        """Return the parts of a table in order catalog, db, table."""
2560        parts: t.List[Expression] = []
2561
2562        for arg in ("catalog", "db", "this"):
2563            part = self.args.get(arg)
2564
2565            if isinstance(part, Dot):
2566                parts.extend(part.flatten())
2567            elif isinstance(part, Expression):
2568                parts.append(part)
2569
2570        return parts
2571
2572
2573class Union(Subqueryable):
2574    arg_types = {
2575        "with": False,
2576        "this": True,
2577        "expression": True,
2578        "distinct": False,
2579        "by_name": False,
2580        **QUERY_MODIFIERS,
2581    }
2582
2583    def limit(
2584        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2585    ) -> Select:
2586        """
2587        Set the LIMIT expression.
2588
2589        Example:
2590            >>> select("1").union(select("1")).limit(1).sql()
2591            'SELECT * FROM (SELECT 1 UNION SELECT 1) AS _l_0 LIMIT 1'
2592
2593        Args:
2594            expression: the SQL code string to parse.
2595                This can also be an integer.
2596                If a `Limit` instance is passed, this is used as-is.
2597                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2598            dialect: the dialect used to parse the input expression.
2599            copy: if `False`, modify this expression instance in-place.
2600            opts: other options to use to parse the input expressions.
2601
2602        Returns:
2603            The limited subqueryable.
2604        """
2605        return (
2606            select("*")
2607            .from_(self.subquery(alias="_l_0", copy=copy))
2608            .limit(expression, dialect=dialect, copy=False, **opts)
2609        )
2610
2611    def select(
2612        self,
2613        *expressions: t.Optional[ExpOrStr],
2614        append: bool = True,
2615        dialect: DialectType = None,
2616        copy: bool = True,
2617        **opts,
2618    ) -> Union:
2619        """Append to or set the SELECT of the union recursively.
2620
2621        Example:
2622            >>> from sqlglot import parse_one
2623            >>> parse_one("select a from x union select a from y union select a from z").select("b").sql()
2624            'SELECT a, b FROM x UNION SELECT a, b FROM y UNION SELECT a, b FROM z'
2625
2626        Args:
2627            *expressions: the SQL code strings to parse.
2628                If an `Expression` instance is passed, it will be used as-is.
2629            append: if `True`, add to any existing expressions.
2630                Otherwise, this resets the expressions.
2631            dialect: the dialect used to parse the input expressions.
2632            copy: if `False`, modify this expression instance in-place.
2633            opts: other options to use to parse the input expressions.
2634
2635        Returns:
2636            Union: the modified expression.
2637        """
2638        this = self.copy() if copy else self
2639        this.this.unnest().select(*expressions, append=append, dialect=dialect, copy=False, **opts)
2640        this.expression.unnest().select(
2641            *expressions, append=append, dialect=dialect, copy=False, **opts
2642        )
2643        return this
2644
2645    @property
2646    def named_selects(self) -> t.List[str]:
2647        return self.this.unnest().named_selects
2648
2649    @property
2650    def is_star(self) -> bool:
2651        return self.this.is_star or self.expression.is_star
2652
2653    @property
2654    def selects(self) -> t.List[Expression]:
2655        return self.this.unnest().selects
2656
2657    @property
2658    def left(self) -> Expression:
2659        return self.this
2660
2661    @property
2662    def right(self) -> Expression:
2663        return self.expression
2664
2665
2666class Except(Union):
2667    pass
2668
2669
2670class Intersect(Union):
2671    pass
2672
2673
2674class Unnest(UDTF):
2675    arg_types = {
2676        "expressions": True,
2677        "alias": False,
2678        "offset": False,
2679    }
2680
2681
2682class Update(Expression):
2683    arg_types = {
2684        "with": False,
2685        "this": False,
2686        "expressions": True,
2687        "from": False,
2688        "where": False,
2689        "returning": False,
2690        "order": False,
2691        "limit": False,
2692    }
2693
2694
2695class Values(UDTF):
2696    arg_types = {"expressions": True, "alias": False}
2697
2698
2699class Var(Expression):
2700    pass
2701
2702
2703class Version(Expression):
2704    """
2705    Time travel, iceberg, bigquery etc
2706    https://trino.io/docs/current/connector/iceberg.html?highlight=snapshot#using-snapshots
2707    https://www.databricks.com/blog/2019/02/04/introducing-delta-time-travel-for-large-scale-data-lakes.html
2708    https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax#for_system_time_as_of
2709    https://learn.microsoft.com/en-us/sql/relational-databases/tables/querying-data-in-a-system-versioned-temporal-table?view=sql-server-ver16
2710    this is either TIMESTAMP or VERSION
2711    kind is ("AS OF", "BETWEEN")
2712    """
2713
2714    arg_types = {"this": True, "kind": True, "expression": False}
2715
2716
2717class Schema(Expression):
2718    arg_types = {"this": False, "expressions": False}
2719
2720
2721# https://dev.mysql.com/doc/refman/8.0/en/select.html
2722# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SELECT.html
2723class Lock(Expression):
2724    arg_types = {"update": True, "expressions": False, "wait": False}
2725
2726
2727class Select(Subqueryable):
2728    arg_types = {
2729        "with": False,
2730        "kind": False,
2731        "expressions": False,
2732        "hint": False,
2733        "distinct": False,
2734        "into": False,
2735        "from": False,
2736        **QUERY_MODIFIERS,
2737    }
2738
2739    def from_(
2740        self, expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
2741    ) -> Select:
2742        """
2743        Set the FROM expression.
2744
2745        Example:
2746            >>> Select().from_("tbl").select("x").sql()
2747            'SELECT x FROM tbl'
2748
2749        Args:
2750            expression : the SQL code strings to parse.
2751                If a `From` instance is passed, this is used as-is.
2752                If another `Expression` instance is passed, it will be wrapped in a `From`.
2753            dialect: the dialect used to parse the input expression.
2754            copy: if `False`, modify this expression instance in-place.
2755            opts: other options to use to parse the input expressions.
2756
2757        Returns:
2758            The modified Select expression.
2759        """
2760        return _apply_builder(
2761            expression=expression,
2762            instance=self,
2763            arg="from",
2764            into=From,
2765            prefix="FROM",
2766            dialect=dialect,
2767            copy=copy,
2768            **opts,
2769        )
2770
2771    def group_by(
2772        self,
2773        *expressions: t.Optional[ExpOrStr],
2774        append: bool = True,
2775        dialect: DialectType = None,
2776        copy: bool = True,
2777        **opts,
2778    ) -> Select:
2779        """
2780        Set the GROUP BY expression.
2781
2782        Example:
2783            >>> Select().from_("tbl").select("x", "COUNT(1)").group_by("x").sql()
2784            'SELECT x, COUNT(1) FROM tbl GROUP BY x'
2785
2786        Args:
2787            *expressions: the SQL code strings to parse.
2788                If a `Group` instance is passed, this is used as-is.
2789                If another `Expression` instance is passed, it will be wrapped in a `Group`.
2790                If nothing is passed in then a group by is not applied to the expression
2791            append: if `True`, add to any existing expressions.
2792                Otherwise, this flattens all the `Group` expression into a single expression.
2793            dialect: the dialect used to parse the input expression.
2794            copy: if `False`, modify this expression instance in-place.
2795            opts: other options to use to parse the input expressions.
2796
2797        Returns:
2798            The modified Select expression.
2799        """
2800        if not expressions:
2801            return self if not copy else self.copy()
2802
2803        return _apply_child_list_builder(
2804            *expressions,
2805            instance=self,
2806            arg="group",
2807            append=append,
2808            copy=copy,
2809            prefix="GROUP BY",
2810            into=Group,
2811            dialect=dialect,
2812            **opts,
2813        )
2814
2815    def order_by(
2816        self,
2817        *expressions: t.Optional[ExpOrStr],
2818        append: bool = True,
2819        dialect: DialectType = None,
2820        copy: bool = True,
2821        **opts,
2822    ) -> Select:
2823        """
2824        Set the ORDER BY expression.
2825
2826        Example:
2827            >>> Select().from_("tbl").select("x").order_by("x DESC").sql()
2828            'SELECT x FROM tbl ORDER BY x DESC'
2829
2830        Args:
2831            *expressions: the SQL code strings to parse.
2832                If a `Group` instance is passed, this is used as-is.
2833                If another `Expression` instance is passed, it will be wrapped in a `Order`.
2834            append: if `True`, add to any existing expressions.
2835                Otherwise, this flattens all the `Order` expression into a single expression.
2836            dialect: the dialect used to parse the input expression.
2837            copy: if `False`, modify this expression instance in-place.
2838            opts: other options to use to parse the input expressions.
2839
2840        Returns:
2841            The modified Select expression.
2842        """
2843        return _apply_child_list_builder(
2844            *expressions,
2845            instance=self,
2846            arg="order",
2847            append=append,
2848            copy=copy,
2849            prefix="ORDER BY",
2850            into=Order,
2851            dialect=dialect,
2852            **opts,
2853        )
2854
2855    def sort_by(
2856        self,
2857        *expressions: t.Optional[ExpOrStr],
2858        append: bool = True,
2859        dialect: DialectType = None,
2860        copy: bool = True,
2861        **opts,
2862    ) -> Select:
2863        """
2864        Set the SORT BY expression.
2865
2866        Example:
2867            >>> Select().from_("tbl").select("x").sort_by("x DESC").sql(dialect="hive")
2868            'SELECT x FROM tbl SORT BY x DESC'
2869
2870        Args:
2871            *expressions: the SQL code strings to parse.
2872                If a `Group` instance is passed, this is used as-is.
2873                If another `Expression` instance is passed, it will be wrapped in a `SORT`.
2874            append: if `True`, add to any existing expressions.
2875                Otherwise, this flattens all the `Order` expression into a single expression.
2876            dialect: the dialect used to parse the input expression.
2877            copy: if `False`, modify this expression instance in-place.
2878            opts: other options to use to parse the input expressions.
2879
2880        Returns:
2881            The modified Select expression.
2882        """
2883        return _apply_child_list_builder(
2884            *expressions,
2885            instance=self,
2886            arg="sort",
2887            append=append,
2888            copy=copy,
2889            prefix="SORT BY",
2890            into=Sort,
2891            dialect=dialect,
2892            **opts,
2893        )
2894
2895    def cluster_by(
2896        self,
2897        *expressions: t.Optional[ExpOrStr],
2898        append: bool = True,
2899        dialect: DialectType = None,
2900        copy: bool = True,
2901        **opts,
2902    ) -> Select:
2903        """
2904        Set the CLUSTER BY expression.
2905
2906        Example:
2907            >>> Select().from_("tbl").select("x").cluster_by("x DESC").sql(dialect="hive")
2908            'SELECT x FROM tbl CLUSTER BY x DESC'
2909
2910        Args:
2911            *expressions: the SQL code strings to parse.
2912                If a `Group` instance is passed, this is used as-is.
2913                If another `Expression` instance is passed, it will be wrapped in a `Cluster`.
2914            append: if `True`, add to any existing expressions.
2915                Otherwise, this flattens all the `Order` expression into a single expression.
2916            dialect: the dialect used to parse the input expression.
2917            copy: if `False`, modify this expression instance in-place.
2918            opts: other options to use to parse the input expressions.
2919
2920        Returns:
2921            The modified Select expression.
2922        """
2923        return _apply_child_list_builder(
2924            *expressions,
2925            instance=self,
2926            arg="cluster",
2927            append=append,
2928            copy=copy,
2929            prefix="CLUSTER BY",
2930            into=Cluster,
2931            dialect=dialect,
2932            **opts,
2933        )
2934
2935    def limit(
2936        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2937    ) -> Select:
2938        """
2939        Set the LIMIT expression.
2940
2941        Example:
2942            >>> Select().from_("tbl").select("x").limit(10).sql()
2943            'SELECT x FROM tbl LIMIT 10'
2944
2945        Args:
2946            expression: the SQL code string to parse.
2947                This can also be an integer.
2948                If a `Limit` instance is passed, this is used as-is.
2949                If another `Expression` instance is passed, it will be wrapped in a `Limit`.
2950            dialect: the dialect used to parse the input expression.
2951            copy: if `False`, modify this expression instance in-place.
2952            opts: other options to use to parse the input expressions.
2953
2954        Returns:
2955            Select: the modified expression.
2956        """
2957        return _apply_builder(
2958            expression=expression,
2959            instance=self,
2960            arg="limit",
2961            into=Limit,
2962            prefix="LIMIT",
2963            dialect=dialect,
2964            copy=copy,
2965            into_arg="expression",
2966            **opts,
2967        )
2968
2969    def offset(
2970        self, expression: ExpOrStr | int, dialect: DialectType = None, copy: bool = True, **opts
2971    ) -> Select:
2972        """
2973        Set the OFFSET expression.
2974
2975        Example:
2976            >>> Select().from_("tbl").select("x").offset(10).sql()
2977            'SELECT x FROM tbl OFFSET 10'
2978
2979        Args:
2980            expression: the SQL code string to parse.
2981                This can also be an integer.
2982                If a `Offset` instance is passed, this is used as-is.
2983                If another `Expression` instance is passed, it will be wrapped in a `Offset`.
2984            dialect: the dialect used to parse the input expression.
2985            copy: if `False`, modify this expression instance in-place.
2986            opts: other options to use to parse the input expressions.
2987
2988        Returns:
2989            The modified Select expression.
2990        """
2991        return _apply_builder(
2992            expression=expression,
2993            instance=self,
2994            arg="offset",
2995            into=Offset,
2996            prefix="OFFSET",
2997            dialect=dialect,
2998            copy=copy,
2999            into_arg="expression",
3000            **opts,
3001        )
3002
3003    def select(
3004        self,
3005        *expressions: t.Optional[ExpOrStr],
3006        append: bool = True,
3007        dialect: DialectType = None,
3008        copy: bool = True,
3009        **opts,
3010    ) -> Select:
3011        """
3012        Append to or set the SELECT expressions.
3013
3014        Example:
3015            >>> Select().select("x", "y").sql()
3016            'SELECT x, y'
3017
3018        Args:
3019            *expressions: the SQL code strings to parse.
3020                If an `Expression` instance is passed, it will be used as-is.
3021            append: if `True`, add to any existing expressions.
3022                Otherwise, this resets the expressions.
3023            dialect: the dialect used to parse the input expressions.
3024            copy: if `False`, modify this expression instance in-place.
3025            opts: other options to use to parse the input expressions.
3026
3027        Returns:
3028            The modified Select expression.
3029        """
3030        return _apply_list_builder(
3031            *expressions,
3032            instance=self,
3033            arg="expressions",
3034            append=append,
3035            dialect=dialect,
3036            copy=copy,
3037            **opts,
3038        )
3039
3040    def lateral(
3041        self,
3042        *expressions: t.Optional[ExpOrStr],
3043        append: bool = True,
3044        dialect: DialectType = None,
3045        copy: bool = True,
3046        **opts,
3047    ) -> Select:
3048        """
3049        Append to or set the LATERAL expressions.
3050
3051        Example:
3052            >>> Select().select("x").lateral("OUTER explode(y) tbl2 AS z").from_("tbl").sql()
3053            'SELECT x FROM tbl LATERAL VIEW OUTER EXPLODE(y) tbl2 AS z'
3054
3055        Args:
3056            *expressions: the SQL code strings to parse.
3057                If an `Expression` instance is passed, it will be used as-is.
3058            append: if `True`, add to any existing expressions.
3059                Otherwise, this resets the expressions.
3060            dialect: the dialect used to parse the input expressions.
3061            copy: if `False`, modify this expression instance in-place.
3062            opts: other options to use to parse the input expressions.
3063
3064        Returns:
3065            The modified Select expression.
3066        """
3067        return _apply_list_builder(
3068            *expressions,
3069            instance=self,
3070            arg="laterals",
3071            append=append,
3072            into=Lateral,
3073            prefix="LATERAL VIEW",
3074            dialect=dialect,
3075            copy=copy,
3076            **opts,
3077        )
3078
3079    def join(
3080        self,
3081        expression: ExpOrStr,
3082        on: t.Optional[ExpOrStr] = None,
3083        using: t.Optional[ExpOrStr | t.Collection[ExpOrStr]] = None,
3084        append: bool = True,
3085        join_type: t.Optional[str] = None,
3086        join_alias: t.Optional[Identifier | str] = None,
3087        dialect: DialectType = None,
3088        copy: bool = True,
3089        **opts,
3090    ) -> Select:
3091        """
3092        Append to or set the JOIN expressions.
3093
3094        Example:
3095            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y").sql()
3096            'SELECT * FROM tbl JOIN tbl2 ON tbl1.y = tbl2.y'
3097
3098            >>> Select().select("1").from_("a").join("b", using=["x", "y", "z"]).sql()
3099            'SELECT 1 FROM a JOIN b USING (x, y, z)'
3100
3101            Use `join_type` to change the type of join:
3102
3103            >>> Select().select("*").from_("tbl").join("tbl2", on="tbl1.y = tbl2.y", join_type="left outer").sql()
3104            'SELECT * FROM tbl LEFT OUTER JOIN tbl2 ON tbl1.y = tbl2.y'
3105
3106        Args:
3107            expression: the SQL code string to parse.
3108                If an `Expression` instance is passed, it will be used as-is.
3109            on: optionally specify the join "on" criteria as a SQL string.
3110                If an `Expression` instance is passed, it will be used as-is.
3111            using: optionally specify the join "using" criteria as a SQL string.
3112                If an `Expression` instance is passed, it will be used as-is.
3113            append: if `True`, add to any existing expressions.
3114                Otherwise, this resets the expressions.
3115            join_type: if set, alter the parsed join type.
3116            join_alias: an optional alias for the joined source.
3117            dialect: the dialect used to parse the input expressions.
3118            copy: if `False`, modify this expression instance in-place.
3119            opts: other options to use to parse the input expressions.
3120
3121        Returns:
3122            Select: the modified expression.
3123        """
3124        parse_args: t.Dict[str, t.Any] = {"dialect": dialect, **opts}
3125
3126        try:
3127            expression = maybe_parse(expression, into=Join, prefix="JOIN", **parse_args)
3128        except ParseError:
3129            expression = maybe_parse(expression, into=(Join, Expression), **parse_args)
3130
3131        join = expression if isinstance(expression, Join) else Join(this=expression)
3132
3133        if isinstance(join.this, Select):
3134            join.this.replace(join.this.subquery())
3135
3136        if join_type:
3137            method: t.Optional[Token]
3138            side: t.Optional[Token]
3139            kind: t.Optional[Token]
3140
3141            method, side, kind = maybe_parse(join_type, into="JOIN_TYPE", **parse_args)  # type: ignore
3142
3143            if method:
3144                join.set("method", method.text)
3145            if side:
3146                join.set("side", side.text)
3147            if kind:
3148                join.set("kind", kind.text)
3149
3150        if on:
3151            on = and_(*ensure_list(on), dialect=dialect, copy=copy, **opts)
3152            join.set("on", on)
3153
3154        if using:
3155            join = _apply_list_builder(
3156                *ensure_list(using),
3157                instance=join,
3158                arg="using",
3159                append=append,
3160                copy=copy,
3161                into=Identifier,
3162                **opts,
3163            )
3164
3165        if join_alias:
3166            join.set("this", alias_(join.this, join_alias, table=True))
3167
3168        return _apply_list_builder(
3169            join,
3170            instance=self,
3171            arg="joins",
3172            append=append,
3173            copy=copy,
3174            **opts,
3175        )
3176
3177    def where(
3178        self,
3179        *expressions: t.Optional[ExpOrStr],
3180        append: bool = True,
3181        dialect: DialectType = None,
3182        copy: bool = True,
3183        **opts,
3184    ) -> Select:
3185        """
3186        Append to or set the WHERE expressions.
3187
3188        Example:
3189            >>> Select().select("x").from_("tbl").where("x = 'a' OR x < 'b'").sql()
3190            "SELECT x FROM tbl WHERE x = 'a' OR x < 'b'"
3191
3192        Args:
3193            *expressions: the SQL code strings to parse.
3194                If an `Expression` instance is passed, it will be used as-is.
3195                Multiple expressions are combined with an AND operator.
3196            append: if `True`, AND the new expressions to any existing expression.
3197                Otherwise, this resets the expression.
3198            dialect: the dialect used to parse the input expressions.
3199            copy: if `False`, modify this expression instance in-place.
3200            opts: other options to use to parse the input expressions.
3201
3202        Returns:
3203            Select: the modified expression.
3204        """
3205        return _apply_conjunction_builder(
3206            *expressions,
3207            instance=self,
3208            arg="where",
3209            append=append,
3210            into=Where,
3211            dialect=dialect,
3212            copy=copy,
3213            **opts,
3214        )
3215
3216    def having(
3217        self,
3218        *expressions: t.Optional[ExpOrStr],
3219        append: bool = True,
3220        dialect: DialectType = None,
3221        copy: bool = True,
3222        **opts,
3223    ) -> Select:
3224        """
3225        Append to or set the HAVING expressions.
3226
3227        Example:
3228            >>> Select().select("x", "COUNT(y)").from_("tbl").group_by("x").having("COUNT(y) > 3").sql()
3229            'SELECT x, COUNT(y) FROM tbl GROUP BY x HAVING COUNT(y) > 3'
3230
3231        Args:
3232            *expressions: the SQL code strings to parse.
3233                If an `Expression` instance is passed, it will be used as-is.
3234                Multiple expressions are combined with an AND operator.
3235            append: if `True`, AND the new expressions to any existing expression.
3236                Otherwise, this resets the expression.
3237            dialect: the dialect used to parse the input expressions.
3238            copy: if `False`, modify this expression instance in-place.
3239            opts: other options to use to parse the input expressions.
3240
3241        Returns:
3242            The modified Select expression.
3243        """
3244        return _apply_conjunction_builder(
3245            *expressions,
3246            instance=self,
3247            arg="having",
3248            append=append,
3249            into=Having,
3250            dialect=dialect,
3251            copy=copy,
3252            **opts,
3253        )
3254
3255    def window(
3256        self,
3257        *expressions: t.Optional[ExpOrStr],
3258        append: bool = True,
3259        dialect: DialectType = None,
3260        copy: bool = True,
3261        **opts,
3262    ) -> Select:
3263        return _apply_list_builder(
3264            *expressions,
3265            instance=self,
3266            arg="windows",
3267            append=append,
3268            into=Window,
3269            dialect=dialect,
3270            copy=copy,
3271            **opts,
3272        )
3273
3274    def qualify(
3275        self,
3276        *expressions: t.Optional[ExpOrStr],
3277        append: bool = True,
3278        dialect: DialectType = None,
3279        copy: bool = True,
3280        **opts,
3281    ) -> Select:
3282        return _apply_conjunction_builder(
3283            *expressions,
3284            instance=self,
3285            arg="qualify",
3286            append=append,
3287            into=Qualify,
3288            dialect=dialect,
3289            copy=copy,
3290            **opts,
3291        )
3292
3293    def distinct(
3294        self, *ons: t.Optional[ExpOrStr], distinct: bool = True, copy: bool = True
3295    ) -> Select:
3296        """
3297        Set the OFFSET expression.
3298
3299        Example:
3300            >>> Select().from_("tbl").select("x").distinct().sql()
3301            'SELECT DISTINCT x FROM tbl'
3302
3303        Args:
3304            ons: the expressions to distinct on
3305            distinct: whether the Select should be distinct
3306            copy: if `False`, modify this expression instance in-place.
3307
3308        Returns:
3309            Select: the modified expression.
3310        """
3311        instance = maybe_copy(self, copy)
3312        on = Tuple(expressions=[maybe_parse(on, copy=copy) for on in ons if on]) if ons else None
3313        instance.set("distinct", Distinct(on=on) if distinct else None)
3314        return instance
3315
3316    def ctas(
3317        self,
3318        table: ExpOrStr,
3319        properties: t.Optional[t.Dict] = None,
3320        dialect: DialectType = None,
3321        copy: bool = True,
3322        **opts,
3323    ) -> Create:
3324        """
3325        Convert this expression to a CREATE TABLE AS statement.
3326
3327        Example:
3328            >>> Select().select("*").from_("tbl").ctas("x").sql()
3329            'CREATE TABLE x AS SELECT * FROM tbl'
3330
3331        Args:
3332            table: the SQL code string to parse as the table name.
3333                If another `Expression` instance is passed, it will be used as-is.
3334            properties: an optional mapping of table properties
3335            dialect: the dialect used to parse the input table.
3336            copy: if `False`, modify this expression instance in-place.
3337            opts: other options to use to parse the input table.
3338
3339        Returns:
3340            The new Create expression.
3341        """
3342        instance = maybe_copy(self, copy)
3343        table_expression = maybe_parse(
3344            table,
3345            into=Table,
3346            dialect=dialect,
3347            **opts,
3348        )
3349        properties_expression = None
3350        if properties:
3351            properties_expression = Properties.from_dict(properties)
3352
3353        return Create(
3354            this=table_expression,
3355            kind="table",
3356            expression=instance,
3357            properties=properties_expression,
3358        )
3359
3360    def lock(self, update: bool = True, copy: bool = True) -> Select:
3361        """
3362        Set the locking read mode for this expression.
3363
3364        Examples:
3365            >>> Select().select("x").from_("tbl").where("x = 'a'").lock().sql("mysql")
3366            "SELECT x FROM tbl WHERE x = 'a' FOR UPDATE"
3367
3368            >>> Select().select("x").from_("tbl").where("x = 'a'").lock(update=False).sql("mysql")
3369            "SELECT x FROM tbl WHERE x = 'a' FOR SHARE"
3370
3371        Args:
3372            update: if `True`, the locking type will be `FOR UPDATE`, else it will be `FOR SHARE`.
3373            copy: if `False`, modify this expression instance in-place.
3374
3375        Returns:
3376            The modified expression.
3377        """
3378        inst = maybe_copy(self, copy)
3379        inst.set("locks", [Lock(update=update)])
3380
3381        return inst
3382
3383    def hint(self, *hints: ExpOrStr, dialect: DialectType = None, copy: bool = True) -> Select:
3384        """
3385        Set hints for this expression.
3386
3387        Examples:
3388            >>> Select().select("x").from_("tbl").hint("BROADCAST(y)").sql(dialect="spark")
3389            'SELECT /*+ BROADCAST(y) */ x FROM tbl'
3390
3391        Args:
3392            hints: The SQL code strings to parse as the hints.
3393                If an `Expression` instance is passed, it will be used as-is.
3394            dialect: The dialect used to parse the hints.
3395            copy: If `False`, modify this expression instance in-place.
3396
3397        Returns:
3398            The modified expression.
3399        """
3400        inst = maybe_copy(self, copy)
3401        inst.set(
3402            "hint", Hint(expressions=[maybe_parse(h, copy=copy, dialect=dialect) for h in hints])
3403        )
3404
3405        return inst
3406
3407    @property
3408    def named_selects(self) -> t.List[str]:
3409        return [e.output_name for e in self.expressions if e.alias_or_name]
3410
3411    @property
3412    def is_star(self) -> bool:
3413        return any(expression.is_star for expression in self.expressions)
3414
3415    @property
3416    def selects(self) -> t.List[Expression]:
3417        return self.expressions
3418
3419
3420class Subquery(DerivedTable, Unionable):
3421    arg_types = {
3422        "this": True,
3423        "alias": False,
3424        "with": False,
3425        **QUERY_MODIFIERS,
3426    }
3427
3428    def unnest(self):
3429        """
3430        Returns the first non subquery.
3431        """
3432        expression = self
3433        while isinstance(expression, Subquery):
3434            expression = expression.this
3435        return expression
3436
3437    def unwrap(self) -> Subquery:
3438        expression = self
3439        while expression.same_parent and expression.is_wrapper:
3440            expression = t.cast(Subquery, expression.parent)
3441        return expression
3442
3443    @property
3444    def is_wrapper(self) -> bool:
3445        """
3446        Whether this Subquery acts as a simple wrapper around another expression.
3447
3448        SELECT * FROM (((SELECT * FROM t)))
3449                      ^
3450                      This corresponds to a "wrapper" Subquery node
3451        """
3452        return all(v is None for k, v in self.args.items() if k != "this")
3453
3454    @property
3455    def is_star(self) -> bool:
3456        return self.this.is_star
3457
3458    @property
3459    def output_name(self) -> str:
3460        return self.alias
3461
3462
3463class TableSample(Expression):
3464    arg_types = {
3465        "this": False,
3466        "expressions": False,
3467        "method": False,
3468        "bucket_numerator": False,
3469        "bucket_denominator": False,
3470        "bucket_field": False,
3471        "percent": False,
3472        "rows": False,
3473        "size": False,
3474        "seed": False,
3475        "kind": False,
3476    }
3477
3478
3479class Tag(Expression):
3480    """Tags are used for generating arbitrary sql like SELECT <span>x</span>."""
3481
3482    arg_types = {
3483        "this": False,
3484        "prefix": False,
3485        "postfix": False,
3486    }
3487
3488
3489# Represents both the standard SQL PIVOT operator and DuckDB's "simplified" PIVOT syntax
3490# https://duckdb.org/docs/sql/statements/pivot
3491class Pivot(Expression):
3492    arg_types = {
3493        "this": False,
3494        "alias": False,
3495        "expressions": False,
3496        "field": False,
3497        "unpivot": False,
3498        "using": False,
3499        "group": False,
3500        "columns": False,
3501        "include_nulls": False,
3502    }
3503
3504
3505class Window(Condition):
3506    arg_types = {
3507        "this": True,
3508        "partition_by": False,
3509        "order": False,
3510        "spec": False,
3511        "alias": False,
3512        "over": False,
3513        "first": False,
3514    }
3515
3516
3517class WindowSpec(Expression):
3518    arg_types = {
3519        "kind": False,
3520        "start": False,
3521        "start_side": False,
3522        "end": False,
3523        "end_side": False,
3524    }
3525
3526
3527class Where(Expression):
3528    pass
3529
3530
3531class Star(Expression):
3532    arg_types = {"except": False, "replace": False}
3533
3534    @property
3535    def name(self) -> str:
3536        return "*"
3537
3538    @property
3539    def output_name(self) -> str:
3540        return self.name
3541
3542
3543class Parameter(Condition):
3544    arg_types = {"this": True, "expression": False}
3545
3546
3547class SessionParameter(Condition):
3548    arg_types = {"this": True, "kind": False}
3549
3550
3551class Placeholder(Condition):
3552    arg_types = {"this": False, "kind": False}
3553
3554
3555class Null(Condition):
3556    arg_types: t.Dict[str, t.Any] = {}
3557
3558    @property
3559    def name(self) -> str:
3560        return "NULL"
3561
3562
3563class Boolean(Condition):
3564    pass
3565
3566
3567class DataTypeParam(Expression):
3568    arg_types = {"this": True, "expression": False}
3569
3570
3571class DataType(Expression):
3572    arg_types = {
3573        "this": True,
3574        "expressions": False,
3575        "nested": False,
3576        "values": False,
3577        "prefix": False,
3578        "kind": False,
3579    }
3580
3581    class Type(AutoName):
3582        ARRAY = auto()
3583        BIGDECIMAL = auto()
3584        BIGINT = auto()
3585        BIGSERIAL = auto()
3586        BINARY = auto()
3587        BIT = auto()
3588        BOOLEAN = auto()
3589        CHAR = auto()
3590        DATE = auto()
3591        DATEMULTIRANGE = auto()
3592        DATERANGE = auto()
3593        DATETIME = auto()
3594        DATETIME64 = auto()
3595        DECIMAL = auto()
3596        DOUBLE = auto()
3597        ENUM = auto()
3598        ENUM8 = auto()
3599        ENUM16 = auto()
3600        FIXEDSTRING = auto()
3601        FLOAT = auto()
3602        GEOGRAPHY = auto()
3603        GEOMETRY = auto()
3604        HLLSKETCH = auto()
3605        HSTORE = auto()
3606        IMAGE = auto()
3607        INET = auto()
3608        INT = auto()
3609        INT128 = auto()
3610        INT256 = auto()
3611        INT4MULTIRANGE = auto()
3612        INT4RANGE = auto()
3613        INT8MULTIRANGE = auto()
3614        INT8RANGE = auto()
3615        INTERVAL = auto()
3616        IPADDRESS = auto()
3617        IPPREFIX = auto()
3618        JSON = auto()
3619        JSONB = auto()
3620        LONGBLOB = auto()
3621        LONGTEXT = auto()
3622        LOWCARDINALITY = auto()
3623        MAP = auto()
3624        MEDIUMBLOB = auto()
3625        MEDIUMINT = auto()
3626        MEDIUMTEXT = auto()
3627        MONEY = auto()
3628        NCHAR = auto()
3629        NESTED = auto()
3630        NULL = auto()
3631        NULLABLE = auto()
3632        NUMMULTIRANGE = auto()
3633        NUMRANGE = auto()
3634        NVARCHAR = auto()
3635        OBJECT = auto()
3636        ROWVERSION = auto()
3637        SERIAL = auto()
3638        SET = auto()
3639        SMALLINT = auto()
3640        SMALLMONEY = auto()
3641        SMALLSERIAL = auto()
3642        STRUCT = auto()
3643        SUPER = auto()
3644        TEXT = auto()
3645        TINYBLOB = auto()
3646        TINYTEXT = auto()
3647        TIME = auto()
3648        TIMETZ = auto()
3649        TIMESTAMP = auto()
3650        TIMESTAMPLTZ = auto()
3651        TIMESTAMPTZ = auto()
3652        TIMESTAMP_S = auto()
3653        TIMESTAMP_MS = auto()
3654        TIMESTAMP_NS = auto()
3655        TINYINT = auto()
3656        TSMULTIRANGE = auto()
3657        TSRANGE = auto()
3658        TSTZMULTIRANGE = auto()
3659        TSTZRANGE = auto()
3660        UBIGINT = auto()
3661        UINT = auto()
3662        UINT128 = auto()
3663        UINT256 = auto()
3664        UMEDIUMINT = auto()
3665        UDECIMAL = auto()
3666        UNIQUEIDENTIFIER = auto()
3667        UNKNOWN = auto()  # Sentinel value, useful for type annotation
3668        USERDEFINED = "USER-DEFINED"
3669        USMALLINT = auto()
3670        UTINYINT = auto()
3671        UUID = auto()
3672        VARBINARY = auto()
3673        VARCHAR = auto()
3674        VARIANT = auto()
3675        XML = auto()
3676        YEAR = auto()
3677
3678    TEXT_TYPES = {
3679        Type.CHAR,
3680        Type.NCHAR,
3681        Type.VARCHAR,
3682        Type.NVARCHAR,
3683        Type.TEXT,
3684    }
3685
3686    INTEGER_TYPES = {
3687        Type.INT,
3688        Type.TINYINT,
3689        Type.SMALLINT,
3690        Type.BIGINT,
3691        Type.INT128,
3692        Type.INT256,
3693        Type.BIT,
3694    }
3695
3696    FLOAT_TYPES = {
3697        Type.FLOAT,
3698        Type.DOUBLE,
3699    }
3700
3701    NUMERIC_TYPES = {
3702        *INTEGER_TYPES,
3703        *FLOAT_TYPES,
3704    }
3705
3706    TEMPORAL_TYPES = {
3707        Type.TIME,
3708        Type.TIMETZ,
3709        Type.TIMESTAMP,
3710        Type.TIMESTAMPTZ,
3711        Type.TIMESTAMPLTZ,
3712        Type.TIMESTAMP_S,
3713        Type.TIMESTAMP_MS,
3714        Type.TIMESTAMP_NS,
3715        Type.DATE,
3716        Type.DATETIME,
3717        Type.DATETIME64,
3718    }
3719
3720    @classmethod
3721    def build(
3722        cls,
3723        dtype: str | DataType | DataType.Type,
3724        dialect: DialectType = None,
3725        udt: bool = False,
3726        **kwargs,
3727    ) -> DataType:
3728        """
3729        Constructs a DataType object.
3730
3731        Args:
3732            dtype: the data type of interest.
3733            dialect: the dialect to use for parsing `dtype`, in case it's a string.
3734            udt: when set to True, `dtype` will be used as-is if it can't be parsed into a
3735                DataType, thus creating a user-defined type.
3736            kawrgs: additional arguments to pass in the constructor of DataType.
3737
3738        Returns:
3739            The constructed DataType object.
3740        """
3741        from sqlglot import parse_one
3742
3743        if isinstance(dtype, str):
3744            if dtype.upper() == "UNKNOWN":
3745                return DataType(this=DataType.Type.UNKNOWN, **kwargs)
3746
3747            try:
3748                data_type_exp = parse_one(
3749                    dtype, read=dialect, into=DataType, error_level=ErrorLevel.IGNORE
3750                )
3751            except ParseError:
3752                if udt:
3753                    return DataType(this=DataType.Type.USERDEFINED, kind=dtype, **kwargs)
3754                raise
3755        elif isinstance(dtype, DataType.Type):
3756            data_type_exp = DataType(this=dtype)
3757        elif isinstance(dtype, DataType):
3758            return dtype
3759        else:
3760            raise ValueError(f"Invalid data type: {type(dtype)}. Expected str or DataType.Type")
3761
3762        return DataType(**{**data_type_exp.args, **kwargs})
3763
3764    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
3765        """
3766        Checks whether this DataType matches one of the provided data types. Nested types or precision
3767        will be compared using "structural equivalence" semantics, so e.g. array<int> != array<float>.
3768
3769        Args:
3770            dtypes: the data types to compare this DataType to.
3771
3772        Returns:
3773            True, if and only if there is a type in `dtypes` which is equal to this DataType.
3774        """
3775        for dtype in dtypes:
3776            other = DataType.build(dtype, udt=True)
3777
3778            if (
3779                other.expressions
3780                or self.this == DataType.Type.USERDEFINED
3781                or other.this == DataType.Type.USERDEFINED
3782            ):
3783                matches = self == other
3784            else:
3785                matches = self.this == other.this
3786
3787            if matches:
3788                return True
3789        return False
3790
3791
3792# https://www.postgresql.org/docs/15/datatype-pseudo.html
3793class PseudoType(DataType):
3794    arg_types = {"this": True}
3795
3796
3797# https://www.postgresql.org/docs/15/datatype-oid.html
3798class ObjectIdentifier(DataType):
3799    arg_types = {"this": True}
3800
3801
3802# WHERE x <OP> EXISTS|ALL|ANY|SOME(SELECT ...)
3803class SubqueryPredicate(Predicate):
3804    pass
3805
3806
3807class All(SubqueryPredicate):
3808    pass
3809
3810
3811class Any(SubqueryPredicate):
3812    pass
3813
3814
3815class Exists(SubqueryPredicate):
3816    pass
3817
3818
3819# Commands to interact with the databases or engines. For most of the command
3820# expressions we parse whatever comes after the command's name as a string.
3821class Command(Expression):
3822    arg_types = {"this": True, "expression": False}
3823
3824
3825class Transaction(Expression):
3826    arg_types = {"this": False, "modes": False, "mark": False}
3827
3828
3829class Commit(Expression):
3830    arg_types = {"chain": False, "this": False, "durability": False}
3831
3832
3833class Rollback(Expression):
3834    arg_types = {"savepoint": False, "this": False}
3835
3836
3837class AlterTable(Expression):
3838    arg_types = {"this": True, "actions": True, "exists": False, "only": False}
3839
3840
3841class AddConstraint(Expression):
3842    arg_types = {"this": False, "expression": False, "enforced": False}
3843
3844
3845class DropPartition(Expression):
3846    arg_types = {"expressions": True, "exists": False}
3847
3848
3849# Binary expressions like (ADD a b)
3850class Binary(Condition):
3851    arg_types = {"this": True, "expression": True}
3852
3853    @property
3854    def left(self) -> Expression:
3855        return self.this
3856
3857    @property
3858    def right(self) -> Expression:
3859        return self.expression
3860
3861
3862class Add(Binary):
3863    pass
3864
3865
3866class Connector(Binary):
3867    pass
3868
3869
3870class And(Connector):
3871    pass
3872
3873
3874class Or(Connector):
3875    pass
3876
3877
3878class BitwiseAnd(Binary):
3879    pass
3880
3881
3882class BitwiseLeftShift(Binary):
3883    pass
3884
3885
3886class BitwiseOr(Binary):
3887    pass
3888
3889
3890class BitwiseRightShift(Binary):
3891    pass
3892
3893
3894class BitwiseXor(Binary):
3895    pass
3896
3897
3898class Div(Binary):
3899    arg_types = {"this": True, "expression": True, "typed": False, "safe": False}
3900
3901
3902class Overlaps(Binary):
3903    pass
3904
3905
3906class Dot(Binary):
3907    @property
3908    def name(self) -> str:
3909        return self.expression.name
3910
3911    @property
3912    def output_name(self) -> str:
3913        return self.name
3914
3915    @classmethod
3916    def build(self, expressions: t.Sequence[Expression]) -> Dot:
3917        """Build a Dot object with a sequence of expressions."""
3918        if len(expressions) < 2:
3919            raise ValueError(f"Dot requires >= 2 expressions.")
3920
3921        return t.cast(Dot, reduce(lambda x, y: Dot(this=x, expression=y), expressions))
3922
3923
3924class DPipe(Binary):
3925    pass
3926
3927
3928class SafeDPipe(DPipe):
3929    pass
3930
3931
3932class EQ(Binary, Predicate):
3933    pass
3934
3935
3936class NullSafeEQ(Binary, Predicate):
3937    pass
3938
3939
3940class NullSafeNEQ(Binary, Predicate):
3941    pass
3942
3943
3944# Represents e.g. := in DuckDB which is mostly used for setting parameters
3945class PropertyEQ(Binary):
3946    pass
3947
3948
3949class Distance(Binary):
3950    pass
3951
3952
3953class Escape(Binary):
3954    pass
3955
3956
3957class Glob(Binary, Predicate):
3958    pass
3959
3960
3961class GT(Binary, Predicate):
3962    pass
3963
3964
3965class GTE(Binary, Predicate):
3966    pass
3967
3968
3969class ILike(Binary, Predicate):
3970    pass
3971
3972
3973class ILikeAny(Binary, Predicate):
3974    pass
3975
3976
3977class IntDiv(Binary):
3978    pass
3979
3980
3981class Is(Binary, Predicate):
3982    pass
3983
3984
3985class Kwarg(Binary):
3986    """Kwarg in special functions like func(kwarg => y)."""
3987
3988
3989class Like(Binary, Predicate):
3990    pass
3991
3992
3993class LikeAny(Binary, Predicate):
3994    pass
3995
3996
3997class LT(Binary, Predicate):
3998    pass
3999
4000
4001class LTE(Binary, Predicate):
4002    pass
4003
4004
4005class Mod(Binary):
4006    pass
4007
4008
4009class Mul(Binary):
4010    pass
4011
4012
4013class NEQ(Binary, Predicate):
4014    pass
4015
4016
4017class SimilarTo(Binary, Predicate):
4018    pass
4019
4020
4021class Slice(Binary):
4022    arg_types = {"this": False, "expression": False}
4023
4024
4025class Sub(Binary):
4026    pass
4027
4028
4029class ArrayOverlaps(Binary):
4030    pass
4031
4032
4033# Unary Expressions
4034# (NOT a)
4035class Unary(Condition):
4036    pass
4037
4038
4039class BitwiseNot(Unary):
4040    pass
4041
4042
4043class Not(Unary):
4044    pass
4045
4046
4047class Paren(Unary):
4048    arg_types = {"this": True, "with": False}
4049
4050    @property
4051    def output_name(self) -> str:
4052        return self.this.name
4053
4054
4055class Neg(Unary):
4056    pass
4057
4058
4059class Alias(Expression):
4060    arg_types = {"this": True, "alias": False}
4061
4062    @property
4063    def output_name(self) -> str:
4064        return self.alias
4065
4066
4067class Aliases(Expression):
4068    arg_types = {"this": True, "expressions": True}
4069
4070    @property
4071    def aliases(self):
4072        return self.expressions
4073
4074
4075class AtTimeZone(Expression):
4076    arg_types = {"this": True, "zone": True}
4077
4078
4079class Between(Predicate):
4080    arg_types = {"this": True, "low": True, "high": True}
4081
4082
4083class Bracket(Condition):
4084    arg_types = {"this": True, "expressions": True}
4085
4086    @property
4087    def output_name(self) -> str:
4088        if len(self.expressions) == 1:
4089            return self.expressions[0].output_name
4090
4091        return super().output_name
4092
4093
4094class SafeBracket(Bracket):
4095    """Represents array lookup where OOB index yields NULL instead of causing a failure."""
4096
4097
4098class Distinct(Expression):
4099    arg_types = {"expressions": False, "on": False}
4100
4101
4102class In(Predicate):
4103    arg_types = {
4104        "this": True,
4105        "expressions": False,
4106        "query": False,
4107        "unnest": False,
4108        "field": False,
4109        "is_global": False,
4110    }
4111
4112
4113# https://cloud.google.com/bigquery/docs/reference/standard-sql/procedural-language#for-in
4114class ForIn(Expression):
4115    arg_types = {"this": True, "expression": True}
4116
4117
4118class TimeUnit(Expression):
4119    """Automatically converts unit arg into a var."""
4120
4121    arg_types = {"unit": False}
4122
4123    UNABBREVIATED_UNIT_NAME = {
4124        "d": "day",
4125        "h": "hour",
4126        "m": "minute",
4127        "ms": "millisecond",
4128        "ns": "nanosecond",
4129        "q": "quarter",
4130        "s": "second",
4131        "us": "microsecond",
4132        "w": "week",
4133        "y": "year",
4134    }
4135
4136    VAR_LIKE = (Column, Literal, Var)
4137
4138    def __init__(self, **args):
4139        unit = args.get("unit")
4140        if isinstance(unit, self.VAR_LIKE):
4141            args["unit"] = Var(this=self.UNABBREVIATED_UNIT_NAME.get(unit.name) or unit.name)
4142        elif isinstance(unit, Week):
4143            unit.set("this", Var(this=unit.this.name))
4144
4145        super().__init__(**args)
4146
4147    @property
4148    def unit(self) -> t.Optional[Var]:
4149        return self.args.get("unit")
4150
4151
4152class IntervalOp(TimeUnit):
4153    arg_types = {"unit": True, "expression": True}
4154
4155    def interval(self):
4156        return Interval(
4157            this=self.expression.copy(),
4158            unit=self.unit.copy(),
4159        )
4160
4161
4162# https://www.oracletutorial.com/oracle-basics/oracle-interval/
4163# https://trino.io/docs/current/language/types.html#interval-day-to-second
4164# https://docs.databricks.com/en/sql/language-manual/data-types/interval-type.html
4165class IntervalSpan(DataType):
4166    arg_types = {"this": True, "expression": True}
4167
4168
4169class Interval(TimeUnit):
4170    arg_types = {"this": False, "unit": False}
4171
4172
4173class IgnoreNulls(Expression):
4174    pass
4175
4176
4177class RespectNulls(Expression):
4178    pass
4179
4180
4181# Functions
4182class Func(Condition):
4183    """
4184    The base class for all function expressions.
4185
4186    Attributes:
4187        is_var_len_args (bool): if set to True the last argument defined in arg_types will be
4188            treated as a variable length argument and the argument's value will be stored as a list.
4189        _sql_names (list): determines the SQL name (1st item in the list) and aliases (subsequent items)
4190            for this function expression. These values are used to map this node to a name during parsing
4191            as well as to provide the function's name during SQL string generation. By default the SQL
4192            name is set to the expression's class name transformed to snake case.
4193    """
4194
4195    is_var_len_args = False
4196
4197    @classmethod
4198    def from_arg_list(cls, args):
4199        if cls.is_var_len_args:
4200            all_arg_keys = list(cls.arg_types)
4201            # If this function supports variable length argument treat the last argument as such.
4202            non_var_len_arg_keys = all_arg_keys[:-1] if cls.is_var_len_args else all_arg_keys
4203            num_non_var = len(non_var_len_arg_keys)
4204
4205            args_dict = {arg_key: arg for arg, arg_key in zip(args, non_var_len_arg_keys)}
4206            args_dict[all_arg_keys[-1]] = args[num_non_var:]
4207        else:
4208            args_dict = {arg_key: arg for arg, arg_key in zip(args, cls.arg_types)}
4209
4210        return cls(**args_dict)
4211
4212    @classmethod
4213    def sql_names(cls):
4214        if cls is Func:
4215            raise NotImplementedError(
4216                "SQL name is only supported by concrete function implementations"
4217            )
4218        if "_sql_names" not in cls.__dict__:
4219            cls._sql_names = [camel_to_snake_case(cls.__name__)]
4220        return cls._sql_names
4221
4222    @classmethod
4223    def sql_name(cls):
4224        return cls.sql_names()[0]
4225
4226    @classmethod
4227    def default_parser_mappings(cls):
4228        return {name: cls.from_arg_list for name in cls.sql_names()}
4229
4230
4231class AggFunc(Func):
4232    pass
4233
4234
4235class ParameterizedAgg(AggFunc):
4236    arg_types = {"this": True, "expressions": True, "params": True}
4237
4238
4239class Abs(Func):
4240    pass
4241
4242
4243class ArgMax(AggFunc):
4244    arg_types = {"this": True, "expression": True, "count": False}
4245    _sql_names = ["ARG_MAX", "ARGMAX", "MAX_BY"]
4246
4247
4248class ArgMin(AggFunc):
4249    arg_types = {"this": True, "expression": True, "count": False}
4250    _sql_names = ["ARG_MIN", "ARGMIN", "MIN_BY"]
4251
4252
4253class ApproxTopK(AggFunc):
4254    arg_types = {"this": True, "expression": False, "counters": False}
4255
4256
4257class Flatten(Func):
4258    pass
4259
4260
4261# https://spark.apache.org/docs/latest/api/sql/index.html#transform
4262class Transform(Func):
4263    arg_types = {"this": True, "expression": True}
4264
4265
4266class Anonymous(Func):
4267    arg_types = {"this": True, "expressions": False}
4268    is_var_len_args = True
4269
4270
4271# https://docs.snowflake.com/en/sql-reference/functions/hll
4272# https://docs.aws.amazon.com/redshift/latest/dg/r_HLL_function.html
4273class Hll(AggFunc):
4274    arg_types = {"this": True, "expressions": False}
4275    is_var_len_args = True
4276
4277
4278class ApproxDistinct(AggFunc):
4279    arg_types = {"this": True, "accuracy": False}
4280    _sql_names = ["APPROX_DISTINCT", "APPROX_COUNT_DISTINCT"]
4281
4282
4283class Array(Func):
4284    arg_types = {"expressions": False}
4285    is_var_len_args = True
4286
4287
4288# https://docs.snowflake.com/en/sql-reference/functions/to_char
4289class ToChar(Func):
4290    arg_types = {"this": True, "format": False}
4291
4292
4293class GenerateSeries(Func):
4294    arg_types = {"start": True, "end": True, "step": False}
4295
4296
4297class ArrayAgg(AggFunc):
4298    pass
4299
4300
4301class ArrayUniqueAgg(AggFunc):
4302    pass
4303
4304
4305class ArrayAll(Func):
4306    arg_types = {"this": True, "expression": True}
4307
4308
4309class ArrayAny(Func):
4310    arg_types = {"this": True, "expression": True}
4311
4312
4313class ArrayConcat(Func):
4314    _sql_names = ["ARRAY_CONCAT", "ARRAY_CAT"]
4315    arg_types = {"this": True, "expressions": False}
4316    is_var_len_args = True
4317
4318
4319class ArrayContains(Binary, Func):
4320    pass
4321
4322
4323class ArrayContained(Binary):
4324    pass
4325
4326
4327class ArrayFilter(Func):
4328    arg_types = {"this": True, "expression": True}
4329    _sql_names = ["FILTER", "ARRAY_FILTER"]
4330
4331
4332class ArrayJoin(Func):
4333    arg_types = {"this": True, "expression": True, "null": False}
4334
4335
4336class ArraySize(Func):
4337    arg_types = {"this": True, "expression": False}
4338
4339
4340class ArraySort(Func):
4341    arg_types = {"this": True, "expression": False}
4342
4343
4344class ArraySum(Func):
4345    pass
4346
4347
4348class ArrayUnionAgg(AggFunc):
4349    pass
4350
4351
4352class Avg(AggFunc):
4353    pass
4354
4355
4356class AnyValue(AggFunc):
4357    arg_types = {"this": True, "having": False, "max": False, "ignore_nulls": False}
4358
4359
4360class First(Func):
4361    arg_types = {"this": True, "ignore_nulls": False}
4362
4363
4364class Last(Func):
4365    arg_types = {"this": True, "ignore_nulls": False}
4366
4367
4368class Case(Func):
4369    arg_types = {"this": False, "ifs": True, "default": False}
4370
4371    def when(self, condition: ExpOrStr, then: ExpOrStr, copy: bool = True, **opts) -> Case:
4372        instance = maybe_copy(self, copy)
4373        instance.append(
4374            "ifs",
4375            If(
4376                this=maybe_parse(condition, copy=copy, **opts),
4377                true=maybe_parse(then, copy=copy, **opts),
4378            ),
4379        )
4380        return instance
4381
4382    def else_(self, condition: ExpOrStr, copy: bool = True, **opts) -> Case:
4383        instance = maybe_copy(self, copy)
4384        instance.set("default", maybe_parse(condition, copy=copy, **opts))
4385        return instance
4386
4387
4388class Cast(Func):
4389    arg_types = {"this": True, "to": True, "format": False, "safe": False}
4390
4391    @property
4392    def name(self) -> str:
4393        return self.this.name
4394
4395    @property
4396    def to(self) -> DataType:
4397        return self.args["to"]
4398
4399    @property
4400    def output_name(self) -> str:
4401        return self.name
4402
4403    def is_type(self, *dtypes: str | DataType | DataType.Type) -> bool:
4404        """
4405        Checks whether this Cast's DataType matches one of the provided data types. Nested types
4406        like arrays or structs will be compared using "structural equivalence" semantics, so e.g.
4407        array<int> != array<float>.
4408
4409        Args:
4410            dtypes: the data types to compare this Cast's DataType to.
4411
4412        Returns:
4413            True, if and only if there is a type in `dtypes` which is equal to this Cast's DataType.
4414        """
4415        return self.to.is_type(*dtypes)
4416
4417
4418class TryCast(Cast):
4419    pass
4420
4421
4422class CastToStrType(Func):
4423    arg_types = {"this": True, "to": True}
4424
4425
4426class Collate(Binary, Func):
4427    pass
4428
4429
4430class Ceil(Func):
4431    arg_types = {"this": True, "decimals": False}
4432    _sql_names = ["CEIL", "CEILING"]
4433
4434
4435class Coalesce(Func):
4436    arg_types = {"this": True, "expressions": False}
4437    is_var_len_args = True
4438    _sql_names = ["COALESCE", "IFNULL", "NVL"]
4439
4440
4441class Chr(Func):
4442    arg_types = {"this": True, "charset": False, "expressions": False}
4443    is_var_len_args = True
4444    _sql_names = ["CHR", "CHAR"]
4445
4446
4447class Concat(Func):
4448    arg_types = {"expressions": True}
4449    is_var_len_args = True
4450
4451
4452class SafeConcat(Concat):
4453    pass
4454
4455
4456class ConcatWs(Concat):
4457    _sql_names = ["CONCAT_WS"]
4458
4459
4460class Count(AggFunc):
4461    arg_types = {"this": False, "expressions": False}
4462    is_var_len_args = True
4463
4464
4465class CountIf(AggFunc):
4466    pass
4467
4468
4469class CurrentDate(Func):
4470    arg_types = {"this": False}
4471
4472
4473class CurrentDatetime(Func):
4474    arg_types = {"this": False}
4475
4476
4477class CurrentTime(Func):
4478    arg_types = {"this": False}
4479
4480
4481class CurrentTimestamp(Func):
4482    arg_types = {"this": False}
4483
4484
4485class CurrentUser(Func):
4486    arg_types = {"this": False}
4487
4488
4489class DateAdd(Func, IntervalOp):
4490    arg_types = {"this": True, "expression": True, "unit": False}
4491
4492
4493class DateSub(Func, IntervalOp):
4494    arg_types = {"this": True, "expression": True, "unit": False}
4495
4496
4497class DateDiff(Func, TimeUnit):
4498    _sql_names = ["DATEDIFF", "DATE_DIFF"]
4499    arg_types = {"this": True, "expression": True, "unit": False}
4500
4501
4502class DateTrunc(Func):
4503    arg_types = {"unit": True, "this": True, "zone": False}
4504
4505    @property
4506    def unit(self) -> Expression:
4507        return self.args["unit"]
4508
4509
4510class DatetimeAdd(Func, IntervalOp):
4511    arg_types = {"this": True, "expression": True, "unit": False}
4512
4513
4514class DatetimeSub(Func, IntervalOp):
4515    arg_types = {"this": True, "expression": True, "unit": False}
4516
4517
4518class DatetimeDiff(Func, TimeUnit):
4519    arg_types = {"this": True, "expression": True, "unit": False}
4520
4521
4522class DatetimeTrunc(Func, TimeUnit):
4523    arg_types = {"this": True, "unit": True, "zone": False}
4524
4525
4526class DayOfWeek(Func):
4527    _sql_names = ["DAY_OF_WEEK", "DAYOFWEEK"]
4528
4529
4530class DayOfMonth(Func):
4531    _sql_names = ["DAY_OF_MONTH", "DAYOFMONTH"]
4532
4533
4534class DayOfYear(Func):
4535    _sql_names = ["DAY_OF_YEAR", "DAYOFYEAR"]
4536
4537
4538class ToDays(Func):
4539    pass
4540
4541
4542class WeekOfYear(Func):
4543    _sql_names = ["WEEK_OF_YEAR", "WEEKOFYEAR"]
4544
4545
4546class MonthsBetween(Func):
4547    arg_types = {"this": True, "expression": True, "roundoff": False}
4548
4549
4550class LastDateOfMonth(Func):
4551    pass
4552
4553
4554class Extract(Func):
4555    arg_types = {"this": True, "expression": True}
4556
4557
4558class Timestamp(Func):
4559    arg_types = {"this": False, "expression": False}
4560
4561
4562class TimestampAdd(Func, TimeUnit):
4563    arg_types = {"this": True, "expression": True, "unit": False}
4564
4565
4566class TimestampSub(Func, TimeUnit):
4567    arg_types = {"this": True, "expression": True, "unit": False}
4568
4569
4570class TimestampDiff(Func, TimeUnit):
4571    arg_types = {"this": True, "expression": True, "unit": False}
4572
4573
4574class TimestampTrunc(Func, TimeUnit):
4575    arg_types = {"this": True, "unit": True, "zone": False}
4576
4577
4578class TimeAdd(Func, TimeUnit):
4579    arg_types = {"this": True, "expression": True, "unit": False}
4580
4581
4582class TimeSub(Func, TimeUnit):
4583    arg_types = {"this": True, "expression": True, "unit": False}
4584
4585
4586class TimeDiff(Func, TimeUnit):
4587    arg_types = {"this": True, "expression": True, "unit": False}
4588
4589
4590class TimeTrunc(Func, TimeUnit):
4591    arg_types = {"this": True, "unit": True, "zone": False}
4592
4593
4594class DateFromParts(Func):
4595    _sql_names = ["DATEFROMPARTS"]
4596    arg_types = {"year": True, "month": True, "day": True}
4597
4598
4599class DateStrToDate(Func):
4600    pass
4601
4602
4603class DateToDateStr(Func):
4604    pass
4605
4606
4607class DateToDi(Func):
4608    pass
4609
4610
4611# https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
4612class Date(Func):
4613    arg_types = {"this": False, "zone": False, "expressions": False}
4614    is_var_len_args = True
4615
4616
4617class Day(Func):
4618    pass
4619
4620
4621class Decode(Func):
4622    arg_types = {"this": True, "charset": True, "replace": False}
4623
4624
4625class DiToDate(Func):
4626    pass
4627
4628
4629class Encode(Func):
4630    arg_types = {"this": True, "charset": True}
4631
4632
4633class Exp(Func):
4634    pass
4635
4636
4637# https://docs.snowflake.com/en/sql-reference/functions/flatten
4638class Explode(Func):
4639    arg_types = {"this": True, "expressions": False}
4640    is_var_len_args = True
4641
4642
4643class ExplodeOuter(Explode):
4644    pass
4645
4646
4647class Posexplode(Explode):
4648    pass
4649
4650
4651class PosexplodeOuter(Posexplode):
4652    pass
4653
4654
4655class Floor(Func):
4656    arg_types = {"this": True, "decimals": False}
4657
4658
4659class FromBase64(Func):
4660    pass
4661
4662
4663class ToBase64(Func):
4664    pass
4665
4666
4667class Greatest(Func):
4668    arg_types = {"this": True, "expressions": False}
4669    is_var_len_args = True
4670
4671
4672class GroupConcat(AggFunc):
4673    arg_types = {"this": True, "separator": False}
4674
4675
4676class Hex(Func):
4677    pass
4678
4679
4680class Xor(Connector, Func):
4681    arg_types = {"this": False, "expression": False, "expressions": False}
4682
4683
4684class If(Func):
4685    arg_types = {"this": True, "true": True, "false": False}
4686
4687
4688class Nullif(Func):
4689    arg_types = {"this": True, "expression": True}
4690
4691
4692class Initcap(Func):
4693    arg_types = {"this": True, "expression": False}
4694
4695
4696class IsNan(Func):
4697    _sql_names = ["IS_NAN", "ISNAN"]
4698
4699
4700class IsInf(Func):
4701    _sql_names = ["IS_INF", "ISINF"]
4702
4703
4704class FormatJson(Expression):
4705    pass
4706
4707
4708class JSONKeyValue(Expression):
4709    arg_types = {"this": True, "expression": True}
4710
4711
4712class JSONObject(Func):
4713    arg_types = {
4714        "expressions": False,
4715        "null_handling": False,
4716        "unique_keys": False,
4717        "return_type": False,
4718        "encoding": False,
4719    }
4720
4721
4722# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAY.html
4723class JSONArray(Func):
4724    arg_types = {
4725        "expressions": True,
4726        "null_handling": False,
4727        "return_type": False,
4728        "strict": False,
4729    }
4730
4731
4732# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_ARRAYAGG.html
4733class JSONArrayAgg(Func):
4734    arg_types = {
4735        "this": True,
4736        "order": False,
4737        "null_handling": False,
4738        "return_type": False,
4739        "strict": False,
4740    }
4741
4742
4743# https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4744# Note: parsing of JSON column definitions is currently incomplete.
4745class JSONColumnDef(Expression):
4746    arg_types = {"this": False, "kind": False, "path": False, "nested_schema": False}
4747
4748
4749class JSONSchema(Expression):
4750    arg_types = {"expressions": True}
4751
4752
4753# # https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/JSON_TABLE.html
4754class JSONTable(Func):
4755    arg_types = {
4756        "this": True,
4757        "schema": True,
4758        "path": False,
4759        "error_handling": False,
4760        "empty_handling": False,
4761    }
4762
4763
4764class OpenJSONColumnDef(Expression):
4765    arg_types = {"this": True, "kind": True, "path": False, "as_json": False}
4766
4767
4768class OpenJSON(Func):
4769    arg_types = {"this": True, "path": False, "expressions": False}
4770
4771
4772class JSONBContains(Binary):
4773    _sql_names = ["JSONB_CONTAINS"]
4774
4775
4776class JSONExtract(Binary, Func):
4777    _sql_names = ["JSON_EXTRACT"]
4778
4779
4780class JSONExtractScalar(JSONExtract):
4781    _sql_names = ["JSON_EXTRACT_SCALAR"]
4782
4783
4784class JSONBExtract(JSONExtract):
4785    _sql_names = ["JSONB_EXTRACT"]
4786
4787
4788class JSONBExtractScalar(JSONExtract):
4789    _sql_names = ["JSONB_EXTRACT_SCALAR"]
4790
4791
4792class JSONFormat(Func):
4793    arg_types = {"this": False, "options": False}
4794    _sql_names = ["JSON_FORMAT"]
4795
4796
4797# https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#operator_member-of
4798class JSONArrayContains(Binary, Predicate, Func):
4799    _sql_names = ["JSON_ARRAY_CONTAINS"]
4800
4801
4802class ParseJSON(Func):
4803    # BigQuery, Snowflake have PARSE_JSON, Presto has JSON_PARSE
4804    _sql_names = ["PARSE_JSON", "JSON_PARSE"]
4805    arg_types = {"this": True, "expressions": False}
4806    is_var_len_args = True
4807
4808
4809class Least(Func):
4810    arg_types = {"this": True, "expressions": False}
4811    is_var_len_args = True
4812
4813
4814class Left(Func):
4815    arg_types = {"this": True, "expression": True}
4816
4817
4818class Right(Func):
4819    arg_types = {"this": True, "expression": True}
4820
4821
4822class Length(Func):
4823    _sql_names = ["LENGTH", "LEN"]
4824
4825
4826class Levenshtein(Func):
4827    arg_types = {
4828        "this": True,
4829        "expression": False,
4830        "ins_cost": False,
4831        "del_cost": False,
4832        "sub_cost": False,
4833    }
4834
4835
4836class Ln(Func):
4837    pass
4838
4839
4840class Log(Func):
4841    arg_types = {"this": True, "expression": False}
4842
4843
4844class Log2(Func):
4845    pass
4846
4847
4848class Log10(Func):
4849    pass
4850
4851
4852class LogicalOr(AggFunc):
4853    _sql_names = ["LOGICAL_OR", "BOOL_OR", "BOOLOR_AGG"]
4854
4855
4856class LogicalAnd(AggFunc):
4857    _sql_names = ["LOGICAL_AND", "BOOL_AND", "BOOLAND_AGG"]
4858
4859
4860class Lower(Func):
4861    _sql_names = ["LOWER", "LCASE"]
4862
4863
4864class Map(Func):
4865    arg_types = {"keys": False, "values": False}
4866
4867    @property
4868    def keys(self) -> t.List[Expression]:
4869        keys = self.args.get("keys")
4870        return keys.expressions if keys else []
4871
4872    @property
4873    def values(self) -> t.List[Expression]:
4874        values = self.args.get("values")
4875        return values.expressions if values else []
4876
4877
4878class MapFromEntries(Func):
4879    pass
4880
4881
4882class StarMap(Func):
4883    pass
4884
4885
4886class VarMap(Func):
4887    arg_types = {"keys": True, "values": True}
4888    is_var_len_args = True
4889
4890    @property
4891    def keys(self) -> t.List[Expression]:
4892        return self.args["keys"].expressions
4893
4894    @property
4895    def values(self) -> t.List[Expression]:
4896        return self.args["values"].expressions
4897
4898
4899# https://dev.mysql.com/doc/refman/8.0/en/fulltext-search.html
4900class MatchAgainst(Func):
4901    arg_types = {"this": True, "expressions": True, "modifier": False}
4902
4903
4904class Max(AggFunc):
4905    arg_types = {"this": True, "expressions": False}
4906    is_var_len_args = True
4907
4908
4909class MD5(Func):
4910    _sql_names = ["MD5"]
4911
4912
4913# Represents the variant of the MD5 function that returns a binary value
4914class MD5Digest(Func):
4915    _sql_names = ["MD5_DIGEST"]
4916
4917
4918class Min(AggFunc):
4919    arg_types = {"this": True, "expressions": False}
4920    is_var_len_args = True
4921
4922
4923class Month(Func):
4924    pass
4925
4926
4927class Nvl2(Func):
4928    arg_types = {"this": True, "true": True, "false": False}
4929
4930
4931# https://cloud.google.com/bigquery/docs/reference/standard-sql/bigqueryml-syntax-predict#mlpredict_function
4932class Predict(Func):
4933    arg_types = {"this": True, "expression": True, "params_struct": False}
4934
4935
4936class Pow(Binary, Func):
4937    _sql_names = ["POWER", "POW"]
4938
4939
4940class PercentileCont(AggFunc):
4941    arg_types = {"this": True, "expression": False}
4942
4943
4944class PercentileDisc(AggFunc):
4945    arg_types = {"this": True, "expression": False}
4946
4947
4948class Quantile(AggFunc):
4949    arg_types = {"this": True, "quantile": True}
4950
4951
4952class ApproxQuantile(Quantile):
4953    arg_types = {"this": True, "quantile": True, "accuracy": False, "weight": False}
4954
4955
4956class RangeN(Func):
4957    arg_types = {"this": True, "expressions": True, "each": False}
4958
4959
4960class ReadCSV(Func):
4961    _sql_names = ["READ_CSV"]
4962    is_var_len_args = True
4963    arg_types = {"this": True, "expressions": False}
4964
4965
4966class Reduce(Func):
4967    arg_types = {"this": True, "initial": True, "merge": True, "finish": False}
4968
4969
4970class RegexpExtract(Func):
4971    arg_types = {
4972        "this": True,
4973        "expression": True,
4974        "position": False,
4975        "occurrence": False,
4976        "parameters": False,
4977        "group": False,
4978    }
4979
4980
4981class RegexpReplace(Func):
4982    arg_types = {
4983        "this": True,
4984        "expression": True,
4985        "replacement": True,
4986        "position": False,
4987        "occurrence": False,
4988        "parameters": False,
4989        "modifiers": False,
4990    }
4991
4992
4993class RegexpLike(Binary, Func):
4994    arg_types = {"this": True, "expression": True, "flag": False}
4995
4996
4997class RegexpILike(Binary, Func):
4998    arg_types = {"this": True, "expression": True, "flag": False}
4999
5000
5001# https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/api/pyspark.sql.functions.split.html
5002# limit is the number of times a pattern is applied
5003class RegexpSplit(Func):
5004    arg_types = {"this": True, "expression": True, "limit": False}
5005
5006
5007class Repeat(Func):
5008    arg_types = {"this": True, "times": True}
5009
5010
5011class Round(Func):
5012    arg_types = {"this": True, "decimals": False}
5013
5014
5015class RowNumber(Func):
5016    arg_types: t.Dict[str, t.Any] = {}
5017
5018
5019class SafeDivide(Func):
5020    arg_types = {"this": True, "expression": True}
5021
5022
5023class SHA(Func):
5024    _sql_names = ["SHA", "SHA1"]
5025
5026
5027class SHA2(Func):
5028    _sql_names = ["SHA2"]
5029    arg_types = {"this": True, "length": False}
5030
5031
5032class SortArray(Func):
5033    arg_types = {"this": True, "asc": False}
5034
5035
5036class Split(Func):
5037    arg_types = {"this": True, "expression": True, "limit": False}
5038
5039
5040# Start may be omitted in the case of postgres
5041# https://www.postgresql.org/docs/9.1/functions-string.html @ Table 9-6
5042class Substring(Func):
5043    arg_types = {"this": True, "start": False, "length": False}
5044
5045
5046class StandardHash(Func):
5047    arg_types = {"this": True, "expression": False}
5048
5049
5050class StartsWith(Func):
5051    _sql_names = ["STARTS_WITH", "STARTSWITH"]
5052    arg_types = {"this": True, "expression": True}
5053
5054
5055class StrPosition(Func):
5056    arg_types = {
5057        "this": True,
5058        "substr": True,
5059        "position": False,
5060        "instance": False,
5061    }
5062
5063
5064class StrToDate(Func):
5065    arg_types = {"this": True, "format": True}
5066
5067
5068class StrToTime(Func):
5069    arg_types = {"this": True, "format": True, "zone": False}
5070
5071
5072# Spark allows unix_timestamp()
5073# https://spark.apache.org/docs/3.1.3/api/python/reference/api/pyspark.sql.functions.unix_timestamp.html
5074class StrToUnix(Func):
5075    arg_types = {"this": False, "format": False}
5076
5077
5078# https://prestodb.io/docs/current/functions/string.html
5079# https://spark.apache.org/docs/latest/api/sql/index.html#str_to_map
5080class StrToMap(Func):
5081    arg_types = {
5082        "this": True,
5083        "pair_delim": False,
5084        "key_value_delim": False,
5085        "duplicate_resolution_callback": False,
5086    }
5087
5088
5089class NumberToStr(Func):
5090    arg_types = {"this": True, "format": True, "culture": False}
5091
5092
5093class FromBase(Func):
5094    arg_types = {"this": True, "expression": True}
5095
5096
5097class Struct(Func):
5098    arg_types = {"expressions": False}
5099    is_var_len_args = True
5100
5101
5102class StructExtract(Func):
5103    arg_types = {"this": True, "expression": True}
5104
5105
5106# https://learn.microsoft.com/en-us/sql/t-sql/functions/stuff-transact-sql?view=sql-server-ver16
5107# https://docs.snowflake.com/en/sql-reference/functions/insert
5108class Stuff(Func):
5109    _sql_names = ["STUFF", "INSERT"]
5110    arg_types = {"this": True, "start": True, "length": True, "expression": True}
5111
5112
5113class Sum(AggFunc):
5114    pass
5115
5116
5117class Sqrt(Func):
5118    pass
5119
5120
5121class Stddev(AggFunc):
5122    pass
5123
5124
5125class StddevPop(AggFunc):
5126    pass
5127
5128
5129class StddevSamp(AggFunc):
5130    pass
5131
5132
5133class TimeToStr(Func):
5134    arg_types = {"this": True, "format": True, "culture": False}
5135
5136
5137class TimeToTimeStr(Func):
5138    pass
5139
5140
5141class TimeToUnix(Func):
5142    pass
5143
5144
5145class TimeStrToDate(Func):
5146    pass
5147
5148
5149class TimeStrToTime(Func):
5150    pass
5151
5152
5153class TimeStrToUnix(Func):
5154    pass
5155
5156
5157class Trim(Func):
5158    arg_types = {
5159        "this": True,
5160        "expression": False,
5161        "position": False,
5162        "collation": False,
5163    }
5164
5165
5166class TsOrDsAdd(Func, TimeUnit):
5167    arg_types = {"this": True, "expression": True, "unit": False}
5168
5169
5170class TsOrDsToDateStr(Func):
5171    pass
5172
5173
5174class TsOrDsToDate(Func):
5175    arg_types = {"this": True, "format": False}
5176
5177
5178class TsOrDiToDi(Func):
5179    pass
5180
5181
5182class Unhex(Func):
5183    pass
5184
5185
5186class UnixToStr(Func):
5187    arg_types = {"this": True, "format": False}
5188
5189
5190# https://prestodb.io/docs/current/functions/datetime.html
5191# presto has weird zone/hours/minutes
5192class UnixToTime(Func):
5193    arg_types = {"this": True, "scale": False, "zone": False, "hours": False, "minutes": False}
5194
5195    SECONDS = Literal.string("seconds")
5196    MILLIS = Literal.string("millis")
5197    MICROS = Literal.string("micros")
5198
5199
5200class UnixToTimeStr(Func):
5201    pass
5202
5203
5204class Upper(Func):
5205    _sql_names = ["UPPER", "UCASE"]
5206
5207
5208class Variance(AggFunc):
5209    _sql_names = ["VARIANCE", "VARIANCE_SAMP", "VAR_SAMP"]
5210
5211
5212class VariancePop(AggFunc):
5213    _sql_names = ["VARIANCE_POP", "VAR_POP"]
5214
5215
5216class Week(Func):
5217    arg_types = {"this": True, "mode": False}
5218
5219
5220class XMLTable(Func):
5221    arg_types = {"this": True, "passing": False, "columns": False, "by_ref": False}
5222
5223
5224class Year(Func):
5225    pass
5226
5227
5228class Use(Expression):
5229    arg_types = {"this": True, "kind": False}
5230
5231
5232class Merge(Expression):
5233    arg_types = {"this": True, "using": True, "on": True, "expressions": True, "with": False}
5234
5235
5236class When(Func):
5237    arg_types = {"matched": True, "source": False, "condition": False, "then": True}
5238
5239
5240# https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnextvaluefor.html
5241# https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql?view=sql-server-ver16
5242class NextValueFor(Func):
5243    arg_types = {"this": True, "order": False}
5244
5245
5246def _norm_arg(arg):
5247    return arg.lower() if type(arg) is str else arg
5248
5249
5250ALL_FUNCTIONS = subclasses(__name__, Func, (AggFunc, Anonymous, Func))
5251
5252
5253# Helpers
5254@t.overload
5255def maybe_parse(
5256    sql_or_expression: ExpOrStr,
5257    *,
5258    into: t.Type[E],
5259    dialect: DialectType = None,
5260    prefix: t.Optional[str] = None,
5261    copy: bool = False,
5262    **opts,
5263) -> E:
5264    ...
5265
5266
5267@t.overload
5268def maybe_parse(
5269    sql_or_expression: str | E,
5270    *,
5271    into: t.Optional[IntoType] = None,
5272    dialect: DialectType = None,
5273    prefix: t.Optional[str] = None,
5274    copy: bool = False,
5275    **opts,
5276) -> E:
5277    ...
5278
5279
5280def maybe_parse(
5281    sql_or_expression: ExpOrStr,
5282    *,
5283    into: t.Optional[IntoType] = None,
5284    dialect: DialectType = None,
5285    prefix: t.Optional[str] = None,
5286    copy: bool = False,
5287    **opts,
5288) -> Expression:
5289    """Gracefully handle a possible string or expression.
5290
5291    Example:
5292        >>> maybe_parse("1")
5293        (LITERAL this: 1, is_string: False)
5294        >>> maybe_parse(to_identifier("x"))
5295        (IDENTIFIER this: x, quoted: False)
5296
5297    Args:
5298        sql_or_expression: the SQL code string or an expression
5299        into: the SQLGlot Expression to parse into
5300        dialect: the dialect used to parse the input expressions (in the case that an
5301            input expression is a SQL string).
5302        prefix: a string to prefix the sql with before it gets parsed
5303            (automatically includes a space)
5304        copy: whether or not to copy the expression.
5305        **opts: other options to use to parse the input expressions (again, in the case
5306            that an input expression is a SQL string).
5307
5308    Returns:
5309        Expression: the parsed or given expression.
5310    """
5311    if isinstance(sql_or_expression, Expression):
5312        if copy:
5313            return sql_or_expression.copy()
5314        return sql_or_expression
5315
5316    if sql_or_expression is None:
5317        raise ParseError(f"SQL cannot be None")
5318
5319    import sqlglot
5320
5321    sql = str(sql_or_expression)
5322    if prefix:
5323        sql = f"{prefix} {sql}"
5324
5325    return sqlglot.parse_one(sql, read=dialect, into=into, **opts)
5326
5327
5328@t.overload
5329def maybe_copy(instance: None, copy: bool = True) -> None:
5330    ...
5331
5332
5333@t.overload
5334def maybe_copy(instance: E, copy: bool = True) -> E:
5335    ...
5336
5337
5338def maybe_copy(instance, copy=True):
5339    return instance.copy() if copy and instance else instance
5340
5341
5342def _is_wrong_expression(expression, into):
5343    return isinstance(expression, Expression) and not isinstance(expression, into)
5344
5345
5346def _apply_builder(
5347    expression,
5348    instance,
5349    arg,
5350    copy=True,
5351    prefix=None,
5352    into=None,
5353    dialect=None,
5354    into_arg="this",
5355    **opts,
5356):
5357    if _is_wrong_expression(expression, into):
5358        expression = into(**{into_arg: expression})
5359    instance = maybe_copy(instance, copy)
5360    expression = maybe_parse(
5361        sql_or_expression=expression,
5362        prefix=prefix,
5363        into=into,
5364        dialect=dialect,
5365        **opts,
5366    )
5367    instance.set(arg, expression)
5368    return instance
5369
5370
5371def _apply_child_list_builder(
5372    *expressions,
5373    instance,
5374    arg,
5375    append=True,
5376    copy=True,
5377    prefix=None,
5378    into=None,
5379    dialect=None,
5380    properties=None,
5381    **opts,
5382):
5383    instance = maybe_copy(instance, copy)
5384    parsed = []
5385    for expression in expressions:
5386        if expression is not None:
5387            if _is_wrong_expression(expression, into):
5388                expression = into(expressions=[expression])
5389
5390            expression = maybe_parse(
5391                expression,
5392                into=into,
5393                dialect=dialect,
5394                prefix=prefix,
5395                **opts,
5396            )
5397            parsed.extend(expression.expressions)
5398
5399    existing = instance.args.get(arg)
5400    if append and existing:
5401        parsed = existing.expressions + parsed
5402
5403    child = into(expressions=parsed)
5404    for k, v in (properties or {}).items():
5405        child.set(k, v)
5406    instance.set(arg, child)
5407
5408    return instance
5409
5410
5411def _apply_list_builder(
5412    *expressions,
5413    instance,
5414    arg,
5415    append=True,
5416    copy=True,
5417    prefix=None,
5418    into=None,
5419    dialect=None,
5420    **opts,
5421):
5422    inst = maybe_copy(instance, copy)
5423
5424    expressions = [
5425        maybe_parse(
5426            sql_or_expression=expression,
5427            into=into,
5428            prefix=prefix,
5429            dialect=dialect,
5430            **opts,
5431        )
5432        for expression in expressions
5433        if expression is not None
5434    ]
5435
5436    existing_expressions = inst.args.get(arg)
5437    if append and existing_expressions:
5438        expressions = existing_expressions + expressions
5439
5440    inst.set(arg, expressions)
5441    return inst
5442
5443
5444def _apply_conjunction_builder(
5445    *expressions,
5446    instance,
5447    arg,
5448    into=None,
5449    append=True,
5450    copy=True,
5451    dialect=None,
5452    **opts,
5453):
5454    expressions = [exp for exp in expressions if exp is not None and exp != ""]
5455    if not expressions:
5456        return instance
5457
5458    inst = maybe_copy(instance, copy)
5459
5460    existing = inst.args.get(arg)
5461    if append and existing is not None:
5462        expressions = [existing.this if into else existing] + list(expressions)
5463
5464    node = and_(*expressions, dialect=dialect, copy=copy, **opts)
5465
5466    inst.set(arg, into(this=node) if into else node)
5467    return inst
5468
5469
5470def _apply_cte_builder(
5471    instance: E,
5472    alias: ExpOrStr,
5473    as_: ExpOrStr,
5474    recursive: t.Optional[bool] = None,
5475    append: bool = True,
5476    dialect: DialectType = None,
5477    copy: bool = True,
5478    **opts,
5479) -> E:
5480    alias_expression = maybe_parse(alias, dialect=dialect, into=TableAlias, **opts)
5481    as_expression = maybe_parse(as_, dialect=dialect, **opts)
5482    cte = CTE(this=as_expression, alias=alias_expression)
5483    return _apply_child_list_builder(
5484        cte,
5485        instance=instance,
5486        arg="with",
5487        append=append,
5488        copy=copy,
5489        into=With,
5490        properties={"recursive": recursive or False},
5491    )
5492
5493
5494def _combine(
5495    expressions: t.Sequence[t.Optional[ExpOrStr]],
5496    operator: t.Type[Connector],
5497    dialect: DialectType = None,
5498    copy: bool = True,
5499    **opts,
5500) -> Expression:
5501    conditions = [
5502        condition(expression, dialect=dialect, copy=copy, **opts)
5503        for expression in expressions
5504        if expression is not None
5505    ]
5506
5507    this, *rest = conditions
5508    if rest:
5509        this = _wrap(this, Connector)
5510    for expression in rest:
5511        this = operator(this=this, expression=_wrap(expression, Connector))
5512
5513    return this
5514
5515
5516def _wrap(expression: E, kind: t.Type[Expression]) -> E | Paren:
5517    return Paren(this=expression) if isinstance(expression, kind) else expression
5518
5519
5520def union(
5521    left: ExpOrStr,
5522    right: ExpOrStr,
5523    distinct: bool = True,
5524    dialect: DialectType = None,
5525    copy: bool = True,
5526    **opts,
5527) -> Union:
5528    """
5529    Initializes a syntax tree from one UNION expression.
5530
5531    Example:
5532        >>> union("SELECT * FROM foo", "SELECT * FROM bla").sql()
5533        'SELECT * FROM foo UNION SELECT * FROM bla'
5534
5535    Args:
5536        left: the SQL code string corresponding to the left-hand side.
5537            If an `Expression` instance is passed, it will be used as-is.
5538        right: the SQL code string corresponding to the right-hand side.
5539            If an `Expression` instance is passed, it will be used as-is.
5540        distinct: set the DISTINCT flag if and only if this is true.
5541        dialect: the dialect used to parse the input expression.
5542        copy: whether or not to copy the expression.
5543        opts: other options to use to parse the input expressions.
5544
5545    Returns:
5546        The new Union instance.
5547    """
5548    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5549    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5550
5551    return Union(this=left, expression=right, distinct=distinct)
5552
5553
5554def intersect(
5555    left: ExpOrStr,
5556    right: ExpOrStr,
5557    distinct: bool = True,
5558    dialect: DialectType = None,
5559    copy: bool = True,
5560    **opts,
5561) -> Intersect:
5562    """
5563    Initializes a syntax tree from one INTERSECT expression.
5564
5565    Example:
5566        >>> intersect("SELECT * FROM foo", "SELECT * FROM bla").sql()
5567        'SELECT * FROM foo INTERSECT SELECT * FROM bla'
5568
5569    Args:
5570        left: the SQL code string corresponding to the left-hand side.
5571            If an `Expression` instance is passed, it will be used as-is.
5572        right: the SQL code string corresponding to the right-hand side.
5573            If an `Expression` instance is passed, it will be used as-is.
5574        distinct: set the DISTINCT flag if and only if this is true.
5575        dialect: the dialect used to parse the input expression.
5576        copy: whether or not to copy the expression.
5577        opts: other options to use to parse the input expressions.
5578
5579    Returns:
5580        The new Intersect instance.
5581    """
5582    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5583    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5584
5585    return Intersect(this=left, expression=right, distinct=distinct)
5586
5587
5588def except_(
5589    left: ExpOrStr,
5590    right: ExpOrStr,
5591    distinct: bool = True,
5592    dialect: DialectType = None,
5593    copy: bool = True,
5594    **opts,
5595) -> Except:
5596    """
5597    Initializes a syntax tree from one EXCEPT expression.
5598
5599    Example:
5600        >>> except_("SELECT * FROM foo", "SELECT * FROM bla").sql()
5601        'SELECT * FROM foo EXCEPT SELECT * FROM bla'
5602
5603    Args:
5604        left: the SQL code string corresponding to the left-hand side.
5605            If an `Expression` instance is passed, it will be used as-is.
5606        right: the SQL code string corresponding to the right-hand side.
5607            If an `Expression` instance is passed, it will be used as-is.
5608        distinct: set the DISTINCT flag if and only if this is true.
5609        dialect: the dialect used to parse the input expression.
5610        copy: whether or not to copy the expression.
5611        opts: other options to use to parse the input expressions.
5612
5613    Returns:
5614        The new Except instance.
5615    """
5616    left = maybe_parse(sql_or_expression=left, dialect=dialect, copy=copy, **opts)
5617    right = maybe_parse(sql_or_expression=right, dialect=dialect, copy=copy, **opts)
5618
5619    return Except(this=left, expression=right, distinct=distinct)
5620
5621
5622def select(*expressions: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5623    """
5624    Initializes a syntax tree from one or multiple SELECT expressions.
5625
5626    Example:
5627        >>> select("col1", "col2").from_("tbl").sql()
5628        'SELECT col1, col2 FROM tbl'
5629
5630    Args:
5631        *expressions: the SQL code string to parse as the expressions of a
5632            SELECT statement. If an Expression instance is passed, this is used as-is.
5633        dialect: the dialect used to parse the input expressions (in the case that an
5634            input expression is a SQL string).
5635        **opts: other options to use to parse the input expressions (again, in the case
5636            that an input expression is a SQL string).
5637
5638    Returns:
5639        Select: the syntax tree for the SELECT statement.
5640    """
5641    return Select().select(*expressions, dialect=dialect, **opts)
5642
5643
5644def from_(expression: ExpOrStr, dialect: DialectType = None, **opts) -> Select:
5645    """
5646    Initializes a syntax tree from a FROM expression.
5647
5648    Example:
5649        >>> from_("tbl").select("col1", "col2").sql()
5650        'SELECT col1, col2 FROM tbl'
5651
5652    Args:
5653        *expression: the SQL code string to parse as the FROM expressions of a
5654            SELECT statement. If an Expression instance is passed, this is used as-is.
5655        dialect: the dialect used to parse the input expression (in the case that the
5656            input expression is a SQL string).
5657        **opts: other options to use to parse the input expressions (again, in the case
5658            that the input expression is a SQL string).
5659
5660    Returns:
5661        Select: the syntax tree for the SELECT statement.
5662    """
5663    return Select().from_(expression, dialect=dialect, **opts)
5664
5665
5666def update(
5667    table: str | Table,
5668    properties: dict,
5669    where: t.Optional[ExpOrStr] = None,
5670    from_: t.Optional[ExpOrStr] = None,
5671    dialect: DialectType = None,
5672    **opts,
5673) -> Update:
5674    """
5675    Creates an update statement.
5676
5677    Example:
5678        >>> update("my_table", {"x": 1, "y": "2", "z": None}, from_="baz", where="id > 1").sql()
5679        "UPDATE my_table SET x = 1, y = '2', z = NULL FROM baz WHERE id > 1"
5680
5681    Args:
5682        *properties: dictionary of properties to set which are
5683            auto converted to sql objects eg None -> NULL
5684        where: sql conditional parsed into a WHERE statement
5685        from_: sql statement parsed into a FROM statement
5686        dialect: the dialect used to parse the input expressions.
5687        **opts: other options to use to parse the input expressions.
5688
5689    Returns:
5690        Update: the syntax tree for the UPDATE statement.
5691    """
5692    update_expr = Update(this=maybe_parse(table, into=Table, dialect=dialect))
5693    update_expr.set(
5694        "expressions",
5695        [
5696            EQ(this=maybe_parse(k, dialect=dialect, **opts), expression=convert(v))
5697            for k, v in properties.items()
5698        ],
5699    )
5700    if from_:
5701        update_expr.set(
5702            "from",
5703            maybe_parse(from_, into=From, dialect=dialect, prefix="FROM", **opts),
5704        )
5705    if isinstance(where, Condition):
5706        where = Where(this=where)
5707    if where:
5708        update_expr.set(
5709            "where",
5710            maybe_parse(where, into=Where, dialect=dialect, prefix="WHERE", **opts),
5711        )
5712    return update_expr
5713
5714
5715def delete(
5716    table: ExpOrStr,
5717    where: t.Optional[ExpOrStr] = None,
5718    returning: t.Optional[ExpOrStr] = None,
5719    dialect: DialectType = None,
5720    **opts,
5721) -> Delete:
5722    """
5723    Builds a delete statement.
5724
5725    Example:
5726        >>> delete("my_table", where="id > 1").sql()
5727        'DELETE FROM my_table WHERE id > 1'
5728
5729    Args:
5730        where: sql conditional parsed into a WHERE statement
5731        returning: sql conditional parsed into a RETURNING statement
5732        dialect: the dialect used to parse the input expressions.
5733        **opts: other options to use to parse the input expressions.
5734
5735    Returns:
5736        Delete: the syntax tree for the DELETE statement.
5737    """
5738    delete_expr = Delete().delete(table, dialect=dialect, copy=False, **opts)
5739    if where:
5740        delete_expr = delete_expr.where(where, dialect=dialect, copy=False, **opts)
5741    if returning:
5742        delete_expr = delete_expr.returning(returning, dialect=dialect, copy=False, **opts)
5743    return delete_expr
5744
5745
5746def insert(
5747    expression: ExpOrStr,
5748    into: ExpOrStr,
5749    columns: t.Optional[t.Sequence[ExpOrStr]] = None,
5750    overwrite: t.Optional[bool] = None,
5751    dialect: DialectType = None,
5752    copy: bool = True,
5753    **opts,
5754) -> Insert:
5755    """
5756    Builds an INSERT statement.
5757
5758    Example:
5759        >>> insert("VALUES (1, 2, 3)", "tbl").sql()
5760        'INSERT INTO tbl VALUES (1, 2, 3)'
5761
5762    Args:
5763        expression: the sql string or expression of the INSERT statement
5764        into: the tbl to insert data to.
5765        columns: optionally the table's column names.
5766        overwrite: whether to INSERT OVERWRITE or not.
5767        dialect: the dialect used to parse the input expressions.
5768        copy: whether or not to copy the expression.
5769        **opts: other options to use to parse the input expressions.
5770
5771    Returns:
5772        Insert: the syntax tree for the INSERT statement.
5773    """
5774    expr = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
5775    this: Table | Schema = maybe_parse(into, into=Table, dialect=dialect, copy=copy, **opts)
5776
5777    if columns:
5778        this = _apply_list_builder(
5779            *columns,
5780            instance=Schema(this=this),
5781            arg="expressions",
5782            into=Identifier,
5783            copy=False,
5784            dialect=dialect,
5785            **opts,
5786        )
5787
5788    return Insert(this=this, expression=expr, overwrite=overwrite)
5789
5790
5791def condition(
5792    expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts
5793) -> Condition:
5794    """
5795    Initialize a logical condition expression.
5796
5797    Example:
5798        >>> condition("x=1").sql()
5799        'x = 1'
5800
5801        This is helpful for composing larger logical syntax trees:
5802        >>> where = condition("x=1")
5803        >>> where = where.and_("y=1")
5804        >>> Select().from_("tbl").select("*").where(where).sql()
5805        'SELECT * FROM tbl WHERE x = 1 AND y = 1'
5806
5807    Args:
5808        *expression: the SQL code string to parse.
5809            If an Expression instance is passed, this is used as-is.
5810        dialect: the dialect used to parse the input expression (in the case that the
5811            input expression is a SQL string).
5812        copy: Whether or not to copy `expression` (only applies to expressions).
5813        **opts: other options to use to parse the input expressions (again, in the case
5814            that the input expression is a SQL string).
5815
5816    Returns:
5817        The new Condition instance
5818    """
5819    return maybe_parse(
5820        expression,
5821        into=Condition,
5822        dialect=dialect,
5823        copy=copy,
5824        **opts,
5825    )
5826
5827
5828def and_(
5829    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5830) -> Condition:
5831    """
5832    Combine multiple conditions with an AND logical operator.
5833
5834    Example:
5835        >>> and_("x=1", and_("y=1", "z=1")).sql()
5836        'x = 1 AND (y = 1 AND z = 1)'
5837
5838    Args:
5839        *expressions: the SQL code strings to parse.
5840            If an Expression instance is passed, this is used as-is.
5841        dialect: the dialect used to parse the input expression.
5842        copy: whether or not to copy `expressions` (only applies to Expressions).
5843        **opts: other options to use to parse the input expressions.
5844
5845    Returns:
5846        And: the new condition
5847    """
5848    return t.cast(Condition, _combine(expressions, And, dialect, copy=copy, **opts))
5849
5850
5851def or_(
5852    *expressions: t.Optional[ExpOrStr], dialect: DialectType = None, copy: bool = True, **opts
5853) -> Condition:
5854    """
5855    Combine multiple conditions with an OR logical operator.
5856
5857    Example:
5858        >>> or_("x=1", or_("y=1", "z=1")).sql()
5859        'x = 1 OR (y = 1 OR z = 1)'
5860
5861    Args:
5862        *expressions: the SQL code strings to parse.
5863            If an Expression instance is passed, this is used as-is.
5864        dialect: the dialect used to parse the input expression.
5865        copy: whether or not to copy `expressions` (only applies to Expressions).
5866        **opts: other options to use to parse the input expressions.
5867
5868    Returns:
5869        Or: the new condition
5870    """
5871    return t.cast(Condition, _combine(expressions, Or, dialect, copy=copy, **opts))
5872
5873
5874def not_(expression: ExpOrStr, dialect: DialectType = None, copy: bool = True, **opts) -> Not:
5875    """
5876    Wrap a condition with a NOT operator.
5877
5878    Example:
5879        >>> not_("this_suit='black'").sql()
5880        "NOT this_suit = 'black'"
5881
5882    Args:
5883        expression: the SQL code string to parse.
5884            If an Expression instance is passed, this is used as-is.
5885        dialect: the dialect used to parse the input expression.
5886        copy: whether to copy the expression or not.
5887        **opts: other options to use to parse the input expressions.
5888
5889    Returns:
5890        The new condition.
5891    """
5892    this = condition(
5893        expression,
5894        dialect=dialect,
5895        copy=copy,
5896        **opts,
5897    )
5898    return Not(this=_wrap(this, Connector))
5899
5900
5901def paren(expression: ExpOrStr, copy: bool = True) -> Paren:
5902    """
5903    Wrap an expression in parentheses.
5904
5905    Example:
5906        >>> paren("5 + 3").sql()
5907        '(5 + 3)'
5908
5909    Args:
5910        expression: the SQL code string to parse.
5911            If an Expression instance is passed, this is used as-is.
5912        copy: whether to copy the expression or not.
5913
5914    Returns:
5915        The wrapped expression.
5916    """
5917    return Paren(this=maybe_parse(expression, copy=copy))
5918
5919
5920SAFE_IDENTIFIER_RE = re.compile(r"^[_a-zA-Z][\w]*$")
5921
5922
5923@t.overload
5924def to_identifier(name: None, quoted: t.Optional[bool] = None, copy: bool = True) -> None:
5925    ...
5926
5927
5928@t.overload
5929def to_identifier(
5930    name: str | Identifier, quoted: t.Optional[bool] = None, copy: bool = True
5931) -> Identifier:
5932    ...
5933
5934
5935def to_identifier(name, quoted=None, copy=True):
5936    """Builds an identifier.
5937
5938    Args:
5939        name: The name to turn into an identifier.
5940        quoted: Whether or not force quote the identifier.
5941        copy: Whether or not to copy name if it's an Identifier.
5942
5943    Returns:
5944        The identifier ast node.
5945    """
5946
5947    if name is None:
5948        return None
5949
5950    if isinstance(name, Identifier):
5951        identifier = maybe_copy(name, copy)
5952    elif isinstance(name, str):
5953        identifier = Identifier(
5954            this=name,
5955            quoted=not SAFE_IDENTIFIER_RE.match(name) if quoted is None else quoted,
5956        )
5957    else:
5958        raise ValueError(f"Name needs to be a string or an Identifier, got: {name.__class__}")
5959    return identifier
5960
5961
5962def parse_identifier(name: str | Identifier, dialect: DialectType = None) -> Identifier:
5963    """
5964    Parses a given string into an identifier.
5965
5966    Args:
5967        name: The name to parse into an identifier.
5968        dialect: The dialect to parse against.
5969
5970    Returns:
5971        The identifier ast node.
5972    """
5973    try:
5974        expression = maybe_parse(name, dialect=dialect, into=Identifier)
5975    except ParseError:
5976        expression = to_identifier(name)
5977
5978    return expression
5979
5980
5981INTERVAL_STRING_RE = re.compile(r"\s*([0-9]+)\s*([a-zA-Z]+)\s*")
5982
5983
5984def to_interval(interval: str | Literal) -> Interval:
5985    """Builds an interval expression from a string like '1 day' or '5 months'."""
5986    if isinstance(interval, Literal):
5987        if not interval.is_string:
5988            raise ValueError("Invalid interval string.")
5989
5990        interval = interval.this
5991
5992    interval_parts = INTERVAL_STRING_RE.match(interval)  # type: ignore
5993
5994    if not interval_parts:
5995        raise ValueError("Invalid interval string.")
5996
5997    return Interval(
5998        this=Literal.string(interval_parts.group(1)),
5999        unit=Var(this=interval_parts.group(2)),
6000    )
6001
6002
6003@t.overload
6004def to_table(sql_path: str | Table, **kwargs) -> Table:
6005    ...
6006
6007
6008@t.overload
6009def to_table(sql_path: None, **kwargs) -> None:
6010    ...
6011
6012
6013def to_table(
6014    sql_path: t.Optional[str | Table], dialect: DialectType = None, **kwargs
6015) -> t.Optional[Table]:
6016    """
6017    Create a table expression from a `[catalog].[schema].[table]` sql path. Catalog and schema are optional.
6018    If a table is passed in then that table is returned.
6019
6020    Args:
6021        sql_path: a `[catalog].[schema].[table]` string.
6022        dialect: the source dialect according to which the table name will be parsed.
6023        kwargs: the kwargs to instantiate the resulting `Table` expression with.
6024
6025    Returns:
6026        A table expression.
6027    """
6028    if sql_path is None or isinstance(sql_path, Table):
6029        return sql_path
6030    if not isinstance(sql_path, str):
6031        raise ValueError(f"Invalid type provided for a table: {type(sql_path)}")
6032
6033    table = maybe_parse(sql_path, into=Table, dialect=dialect)
6034    if table:
6035        for k, v in kwargs.items():
6036            table.set(k, v)
6037
6038    return table
6039
6040
6041def to_column(sql_path: str | Column, **kwargs) -> Column:
6042    """
6043    Create a column from a `[table].[column]` sql path. Schema is optional.
6044
6045    If a column is passed in then that column is returned.
6046
6047    Args:
6048        sql_path: `[table].[column]` string
6049    Returns:
6050        Table: A column expression
6051    """
6052    if sql_path is None or isinstance(sql_path, Column):
6053        return sql_path
6054    if not isinstance(sql_path, str):
6055        raise ValueError(f"Invalid type provided for column: {type(sql_path)}")
6056    return column(*reversed(sql_path.split(".")), **kwargs)  # type: ignore
6057
6058
6059def alias_(
6060    expression: ExpOrStr,
6061    alias: str | Identifier,
6062    table: bool | t.Sequence[str | Identifier] = False,
6063    quoted: t.Optional[bool] = None,
6064    dialect: DialectType = None,
6065    copy: bool = True,
6066    **opts,
6067):
6068    """Create an Alias expression.
6069
6070    Example:
6071        >>> alias_('foo', 'bar').sql()
6072        'foo AS bar'
6073
6074        >>> alias_('(select 1, 2)', 'bar', table=['a', 'b']).sql()
6075        '(SELECT 1, 2) AS bar(a, b)'
6076
6077    Args:
6078        expression: the SQL code strings to parse.
6079            If an Expression instance is passed, this is used as-is.
6080        alias: the alias name to use. If the name has
6081            special characters it is quoted.
6082        table: Whether or not to create a table alias, can also be a list of columns.
6083        quoted: whether or not to quote the alias
6084        dialect: the dialect used to parse the input expression.
6085        copy: Whether or not to copy the expression.
6086        **opts: other options to use to parse the input expressions.
6087
6088    Returns:
6089        Alias: the aliased expression
6090    """
6091    exp = maybe_parse(expression, dialect=dialect, copy=copy, **opts)
6092    alias = to_identifier(alias, quoted=quoted)
6093
6094    if table:
6095        table_alias = TableAlias(this=alias)
6096        exp.set("alias", table_alias)
6097
6098        if not isinstance(table, bool):
6099            for column in table:
6100                table_alias.append("columns", to_identifier(column, quoted=quoted))
6101
6102        return exp
6103
6104    # We don't set the "alias" arg for Window expressions, because that would add an IDENTIFIER node in
6105    # the AST, representing a "named_window" [1] construct (eg. bigquery). What we want is an ALIAS node
6106    # for the complete Window expression.
6107    #
6108    # [1]: https://cloud.google.com/bigquery/docs/reference/standard-sql/window-function-calls
6109
6110    if "alias" in exp.arg_types and not isinstance(exp, Window):
6111        exp.set("alias", alias)
6112        return exp
6113    return Alias(this=exp, alias=alias)
6114
6115
6116def subquery(
6117    expression: ExpOrStr,
6118    alias: t.Optional[Identifier | str] = None,
6119    dialect: DialectType = None,
6120    **opts,
6121) -> Select:
6122    """
6123    Build a subquery expression.
6124
6125    Example:
6126        >>> subquery('select x from tbl', 'bar').select('x').sql()
6127        'SELECT x FROM (SELECT x FROM tbl) AS bar'
6128
6129    Args:
6130        expression: the SQL code strings to parse.
6131            If an Expression instance is passed, this is used as-is.
6132        alias: the alias name to use.
6133        dialect: the dialect used to parse the input expression.
6134        **opts: other options to use to parse the input expressions.
6135
6136    Returns:
6137        A new Select instance with the subquery expression included.
6138    """
6139
6140    expression = maybe_parse(expression, dialect=dialect, **opts).subquery(alias)
6141    return Select().from_(expression, dialect=dialect, **opts)
6142
6143
6144def column(
6145    col: str | Identifier,
6146    table: t.Optional[str | Identifier] = None,
6147    db: t.Optional[str | Identifier] = None,
6148    catalog: t.Optional[str | Identifier] = None,
6149    quoted: t.Optional[bool] = None,
6150) -> Column:
6151    """
6152    Build a Column.
6153
6154    Args:
6155        col: Column name.
6156        table: Table name.
6157        db: Database name.
6158        catalog: Catalog name.
6159        quoted: Whether to force quotes on the column's identifiers.
6160
6161    Returns:
6162        The new Column instance.
6163    """
6164    return Column(
6165        this=to_identifier(col, quoted=quoted),
6166        table=to_identifier(table, quoted=quoted),
6167        db=to_identifier(db, quoted=quoted),
6168        catalog=to_identifier(catalog, quoted=quoted),
6169    )
6170
6171
6172def cast(expression: ExpOrStr, to: str | DataType | DataType.Type, **opts) -> Cast:
6173    """Cast an expression to a data type.
6174
6175    Example:
6176        >>> cast('x + 1', 'int').sql()
6177        'CAST(x + 1 AS INT)'
6178
6179    Args:
6180        expression: The expression to cast.
6181        to: The datatype to cast to.
6182
6183    Returns:
6184        The new Cast instance.
6185    """
6186    expression = maybe_parse(expression, **opts)
6187    data_type = DataType.build(to, **opts)
6188    expression = Cast(this=expression, to=data_type)
6189    expression.type = data_type
6190    return expression
6191
6192
6193def table_(
6194    table: Identifier | str,
6195    db: t.Optional[Identifier | str] = None,
6196    catalog: t.Optional[Identifier | str] = None,
6197    quoted: t.Optional[bool] = None,
6198    alias: t.Optional[Identifier | str] = None,
6199) -> Table:
6200    """Build a Table.
6201
6202    Args:
6203        table: Table name.
6204        db: Database name.
6205        catalog: Catalog name.
6206        quote: Whether to force quotes on the table's identifiers.
6207        alias: Table's alias.
6208
6209    Returns:
6210        The new Table instance.
6211    """
6212    return Table(
6213        this=to_identifier(table, quoted=quoted) if table else None,
6214        db=to_identifier(db, quoted=quoted) if db else None,
6215        catalog=to_identifier(catalog, quoted=quoted) if catalog else None,
6216        alias=TableAlias(this=to_identifier(alias)) if alias else None,
6217    )
6218
6219
6220def values(
6221    values: t.Iterable[t.Tuple[t.Any, ...]],
6222    alias: t.Optional[str] = None,
6223    columns: t.Optional[t.Iterable[str] | t.Dict[str, DataType]] = None,
6224) -> Values:
6225    """Build VALUES statement.
6226
6227    Example:
6228        >>> values([(1, '2')]).sql()
6229        "VALUES (1, '2')"
6230
6231    Args:
6232        values: values statements that will be converted to SQL
6233        alias: optional alias
6234        columns: Optional list of ordered column names or ordered dictionary of column names to types.
6235         If either are provided then an alias is also required.
6236
6237    Returns:
6238        Values: the Values expression object
6239    """
6240    if columns and not alias:
6241        raise ValueError("Alias is required when providing columns")
6242
6243    return Values(
6244        expressions=[convert(tup) for tup in values],
6245        alias=(
6246            TableAlias(this=to_identifier(alias), columns=[to_identifier(x) for x in columns])
6247            if columns
6248            else (TableAlias(this=to_identifier(alias)) if alias else None)
6249        ),
6250    )
6251
6252
6253def var(name: t.Optional[ExpOrStr]) -> Var:
6254    """Build a SQL variable.
6255
6256    Example:
6257        >>> repr(var('x'))
6258        '(VAR this: x)'
6259
6260        >>> repr(var(column('x', table='y')))
6261        '(VAR this: x)'
6262
6263    Args:
6264        name: The name of the var or an expression who's name will become the var.
6265
6266    Returns:
6267        The new variable node.
6268    """
6269    if not name:
6270        raise ValueError("Cannot convert empty name into var.")
6271
6272    if isinstance(name, Expression):
6273        name = name.name
6274    return Var(this=name)
6275
6276
6277def rename_table(old_name: str | Table, new_name: str | Table) -> AlterTable:
6278    """Build ALTER TABLE... RENAME... expression
6279
6280    Args:
6281        old_name: The old name of the table
6282        new_name: The new name of the table
6283
6284    Returns:
6285        Alter table expression
6286    """
6287    old_table = to_table(old_name)
6288    new_table = to_table(new_name)
6289    return AlterTable(
6290        this=old_table,
6291        actions=[
6292            RenameTable(this=new_table),
6293        ],
6294    )
6295
6296
6297def convert(value: t.Any, copy: bool = False) -> Expression:
6298    """Convert a python value into an expression object.
6299
6300    Raises an error if a conversion is not possible.
6301
6302    Args:
6303        value: A python object.
6304        copy: Whether or not to copy `value` (only applies to Expressions and collections).
6305
6306    Returns:
6307        Expression: the equivalent expression object.
6308    """
6309    if isinstance(value, Expression):
6310        return maybe_copy(value, copy)
6311    if isinstance(value, str):
6312        return Literal.string(value)
6313    if isinstance(value, bool):
6314        return Boolean(this=value)
6315    if value is None or (isinstance(value, float) and math.isnan(value)):
6316        return NULL
6317    if isinstance(value, numbers.Number):
6318        return Literal.number(value)
6319    if isinstance(value, datetime.datetime):
6320        datetime_literal = Literal.string(
6321            (value if value.tzinfo else value.replace(tzinfo=datetime.timezone.utc)).isoformat()
6322        )
6323        return TimeStrToTime(this=datetime_literal)
6324    if isinstance(value, datetime.date):
6325        date_literal = Literal.string(value.strftime("%Y-%m-%d"))
6326        return DateStrToDate(this=date_literal)
6327    if isinstance(value, tuple):
6328        return Tuple(expressions=[convert(v, copy=copy) for v in value])
6329    if isinstance(value, list):
6330        return Array(expressions=[convert(v, copy=copy) for v in value])
6331    if isinstance(value, dict):
6332        return Map(
6333            keys=Array(expressions=[convert(k, copy=copy) for k in value]),
6334            values=Array(expressions=[convert(v, copy=copy) for v in value.values()]),
6335        )
6336    raise ValueError(f"Cannot convert {value}")
6337
6338
6339def replace_children(expression: Expression, fun: t.Callable, *args, **kwargs) -> None:
6340    """
6341    Replace children of an expression with the result of a lambda fun(child) -> exp.
6342    """
6343    for k, v in expression.args.items():
6344        is_list_arg = type(v) is list
6345
6346        child_nodes = v if is_list_arg else [v]
6347        new_child_nodes = []
6348
6349        for cn in child_nodes:
6350            if isinstance(cn, Expression):
6351                for child_node in ensure_collection(fun(cn, *args, **kwargs)):
6352                    new_child_nodes.append(child_node)
6353                    child_node.parent = expression
6354                    child_node.arg_key = k
6355            else:
6356                new_child_nodes.append(cn)
6357
6358        expression.args[k] = new_child_nodes if is_list_arg else seq_get(new_child_nodes, 0)
6359
6360
6361def column_table_names(expression: Expression, exclude: str = "") -> t.Set[str]:
6362    """
6363    Return all table names referenced through columns in an expression.
6364
6365    Example:
6366        >>> import sqlglot
6367        >>> sorted(column_table_names(sqlglot.parse_one("a.b AND c.d AND c.e")))
6368        ['a', 'c']
6369
6370    Args:
6371        expression: expression to find table names.
6372        exclude: a table name to exclude
6373
6374    Returns:
6375        A list of unique names.
6376    """
6377    return {
6378        table
6379        for table in (column.table for column in expression.find_all(Column))
6380        if table and table != exclude
6381    }
6382
6383
6384def table_name(table: Table | str, dialect: DialectType = None) -> str:
6385    """Get the full name of a table as a string.
6386
6387    Args:
6388        table: Table expression node or string.
6389        dialect: The dialect to generate the table name for.
6390
6391    Examples:
6392        >>> from sqlglot import exp, parse_one
6393        >>> table_name(parse_one("select * from a.b.c").find(exp.Table))
6394        'a.b.c'
6395
6396    Returns:
6397        The table name.
6398    """
6399
6400    table = maybe_parse(table, into=Table, dialect=dialect)
6401
6402    if not table:
6403        raise ValueError(f"Cannot parse {table}")
6404
6405    return ".".join(
6406        part.sql(dialect=dialect, identify=True)
6407        if not SAFE_IDENTIFIER_RE.match(part.name)
6408        else part.name
6409        for part in table.parts
6410    )
6411
6412
6413def replace_tables(expression: E, mapping: t.Dict[str, str], copy: bool = True) -> E:
6414    """Replace all tables in expression according to the mapping.
6415
6416    Args:
6417        expression: expression node to be transformed and replaced.
6418        mapping: mapping of table names.
6419        copy: whether or not to copy the expression.
6420
6421    Examples:
6422        >>> from sqlglot import exp, parse_one
6423        >>> replace_tables(parse_one("select * from a.b"), {"a.b": "c"}).sql()
6424        'SELECT * FROM c'
6425
6426    Returns:
6427        The mapped expression.
6428    """
6429
6430    def _replace_tables(node: Expression) -> Expression:
6431        if isinstance(node, Table):
6432            new_name = mapping.get(table_name(node))
6433            if new_name:
6434                return to_table(
6435                    new_name,
6436                    **{k: v for k, v in node.args.items() if k not in ("this", "db", "catalog")},
6437                )
6438        return node
6439
6440    return expression.transform(_replace_tables, copy=copy)
6441
6442
6443def replace_placeholders(expression: Expression, *args, **kwargs) -> Expression:
6444    """Replace placeholders in an expression.
6445
6446    Args:
6447        expression: expression node to be transformed and replaced.
6448        args: positional names that will substitute unnamed placeholders in the given order.
6449        kwargs: keyword arguments that will substitute named placeholders.
6450
6451    Examples:
6452        >>> from sqlglot import exp, parse_one
6453        >>> replace_placeholders(
6454        ...     parse_one("select * from :tbl where ? = ?"),
6455        ...     exp.to_identifier("str_col"), "b", tbl=exp.to_identifier("foo")
6456        ... ).sql()
6457        "SELECT * FROM foo WHERE str_col = 'b'"
6458
6459    Returns:
6460        The mapped expression.
6461    """
6462
6463    def _replace_placeholders(node: Expression, args, **kwargs) -> Expression:
6464        if isinstance(node, Placeholder):
6465            if node.name:
6466                new_name = kwargs.get(node.name)
6467                if new_name:
6468                    return convert(new_name)
6469            else:
6470                try:
6471                    return convert(next(args))
6472                except StopIteration:
6473                    pass
6474        return node
6475
6476    return expression.transform(_replace_placeholders, iter(args), **kwargs)
6477
6478
6479def expand(
6480    expression: Expression, sources: t.Dict[str, Subqueryable], copy: bool = True
6481) -> Expression:
6482    """Transforms an expression by expanding all referenced sources into subqueries.
6483
6484    Examples:
6485        >>> from sqlglot import parse_one
6486        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y")}).sql()
6487        'SELECT * FROM (SELECT * FROM y) AS z /* source: x */'
6488
6489        >>> expand(parse_one("select * from x AS z"), {"x": parse_one("select * from y"), "y": parse_one("select * from z")}).sql()
6490        'SELECT * FROM (SELECT * FROM (SELECT * FROM z) AS y /* source: y */) AS z /* source: x */'
6491
6492    Args:
6493        expression: The expression to expand.
6494        sources: A dictionary of name to Subqueryables.
6495        copy: Whether or not to copy the expression during transformation. Defaults to True.
6496
6497    Returns:
6498        The transformed expression.
6499    """
6500
6501    def _expand(node: Expression):
6502        if isinstance(node, Table):
6503            name = table_name(node)
6504            source = sources.get(name)
6505            if source:
6506                subquery = source.subquery(node.alias or name)
6507                subquery.comments = [f"source: {name}"]
6508                return subquery.transform(_expand, copy=False)
6509        return node
6510
6511    return expression.transform(_expand, copy=copy)
6512
6513
6514def func(name: str, *args, dialect: DialectType = None, **kwargs) -> Func:
6515    """
6516    Returns a Func expression.
6517
6518    Examples:
6519        >>> func("abs", 5).sql()
6520        'ABS(5)'
6521
6522        >>> func("cast", this=5, to=DataType.build("DOUBLE")).sql()
6523        'CAST(5 AS DOUBLE)'
6524
6525    Args:
6526        name: the name of the function to build.
6527        args: the args used to instantiate the function of interest.
6528        dialect: the source dialect.
6529        kwargs: the kwargs used to instantiate the function of interest.
6530
6531    Note:
6532        The arguments `args` and `kwargs` are mutually exclusive.
6533
6534    Returns:
6535        An instance of the function of interest, or an anonymous function, if `name` doesn't
6536        correspond to an existing `sqlglot.expressions.Func` class.
6537    """
6538    if args and kwargs:
6539        raise ValueError("Can't use both args and kwargs to instantiate a function.")
6540
6541    from sqlglot.dialects.dialect import Dialect
6542
6543    converted: t.List[Expression] = [maybe_parse(arg, dialect=dialect) for arg in args]
6544    kwargs = {key: maybe_parse(value, dialect=dialect) for key, value in kwargs.items()}
6545
6546    parser = Dialect.get_or_raise(dialect)().parser()
6547    from_args_list = parser.FUNCTIONS.get(name.upper())
6548
6549    if from_args_list:
6550        function = from_args_list(converted) if converted else from_args_list.__self__(**kwargs)  # type: ignore
6551    else:
6552        kwargs = kwargs or {"expressions": converted}
6553        function = Anonymous(this=name, **kwargs)
6554
6555    for error_message in function.error_messages(converted):
6556        raise ValueError(error_message)
6557
6558    return function
6559
6560
6561def case(
6562    expression: t.Optional[ExpOrStr] = None,
6563    **opts,
6564) -> Case:
6565    """
6566    Initialize a CASE statement.
6567
6568    Example:
6569        case().when("a = 1", "foo").else_("bar")
6570
6571    Args:
6572        expression: Optionally, the input expression (not all dialects support this)
6573        **opts: Extra keyword arguments for parsing `expression`
6574    """
6575    if expression is not None:
6576        this = maybe_parse(expression, **opts)
6577    else:
6578        this = None
6579    return Case(this=this, ifs=[])
6580
6581
6582def true() -> Boolean:
6583    """
6584    Returns a true Boolean expression.
6585    """
6586    return Boolean(this=True)
6587
6588
6589def false() -> Boolean:
6590    """
6591    Returns a false Boolean expression.
6592    """
6593    return Boolean(this=False)
6594
6595
6596def null() -> Null:
6597    """
6598    Returns a Null expression.
6599    """
6600    return Null()
6601
6602
6603# TODO: deprecate this
6604TRUE = Boolean(this=True)
6605FALSE = Boolean(this=False)
6606NULL = Null()